Nadine S wrote: > I have a worosheet with cokumns for 20+ payments. Please, if you want to get help from others, make sure that your posting is not full of typos. It makes reading quite hard.
> These columns track the date > it was entered for payment, amount, check date, record number for each of the > 20+ columns. Just out of curiousity: How many columns arel you going to insert into the worksheet? Is there any limit? You should note that Excel 2003 and older can only cope with 255 columns. > In another sheet, I want to sum the amount entered and the amount paid by > line. > Meaning, row 2 on sheet 1 ties to row 2 on sheet 2 so I don't need to do > lookups > or sumif type. At least I don't think so. :) > > > So, I have a formula that sums all the amounts entered but now I need one to > sum > the amounts is there is a check date and record number. I don't quite get what you want to achieve (sorry, but your English is not so good). I guess that what you wanted to say is that you want to get the sum of all payments that have their "Check Date" field set. If so, you can use the following formula for cell Amount Paid: =SUM(IF(Sheet1!ZS(1)<>0,Sheet1!ZS,0),IF(Sheet1!ZS(6)<>0,Sheet1!ZS(5), 0),IF(Sheet1!ZS(11)<>0,Sheet1!ZS(10),0),IF(Sheet1!ZS(16)<>0,Sheet1! ZS(15),0),IF(Sheet1!ZS(21)<>0,Sheet1!ZS(20),0)) Note that this works only for the currently defined columns. If you add more columns, you'll have to edit formulas for "Amount Entered" and "Amount Paid". As there is a limit to the length of a formula, you may get in trouble even before you run out of columns. > I've attached a file as a sample that I think will help you to understand. > Thanks for everyone's help. It looks to me as if your workbook should be able to handle lots of rows (which probably stand for some client) and lots of payments. In that case you'll get sooner of later into trouble if you want to get all data into a single sheet. You can get around this if you (a) use Excel 2010, or (b) use a simple database, for example Access. The later has the benefit that more than one user can enter data. Regards, Stuart -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
