Stuart, first let me thank you for your email. In response to your comment of my few misspellings, let me apologize for my hurried email and therefore lack of running spell check. According to Brain Connection, "proficient readers read most words (except very long ones) principally through the first and last letters and a rapid recognition of the general shape and content of the entire word. That's why we typically read misspelled words with no trouble." My misspellings didn't seem so outrageous to me but maybe that's just me.
In regards to, "How many columns arel you going to insert into the worksheet?" I'm assuming that your word "arel" is supposed to be "are". With that assumption, the answer is "I don't know. As many as needed would have to be my best guess at this point. Currently there are 21 but that may need to increase in time or possibly even decrease. Basically I just don't know." I don't have the option of using Excel 2010, only Excel 2003. As for a database, etc, yes, that's the better way to go but, once again, that is not an option. I need to make this work with the only tool I have which is Excel 2003. Your assumption that this is to handle lots of rows is correct, however, I don't understand what you mean when you said, "(which probably stand for some client)". To clarify for you and everyone else what I need: I have one worksheet (sheet1) that lists 4 fields of data for each payment (20+ payments) across the top of the worksheet. To the left of this is specific row information that is linked to those same places (rows and columns) on sheet2. What I need to show on sheet 2 in column B is the amount that has been entered for payment for the corresponding row on sheet 1. I've done this with a formula that sums all the amounts in sheet 1 in the cells titled "Amount" in the corresponding row on sheet 2 . What I need help with is a writing a formula that will then sum all the amounts in the corresponding row on sheet 1 where a check date and/or record # are recorded next to the amount. Once again, I've attached my original file. The formula I need will be placed on sheet 2 column C. So for the first row containing data on sheet 1 (row2) the total paid would be $1000+$500+$450 for a total formula result of $1950. I hope my English is good enough for everyone to understand what it is I need. Once again, I apologize for my apparently outrageously poor English. Thank you all who are willing to take the time to attempt a solution for me. I truly appreciate all the help I receive from this group. Unfortunately when I put Stuart's formula into cell C2 on sheet 2, I get #NAME? as the result and not $1950. Thanks all. ________________________________ From: Stuart Redmann <dertop...@web.de> To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com> Sent: Wed, August 4, 2010 6:35:31 AM Subject: $$Excel-Macros$$ Re: Need to sum if data is in another cell 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 -- ---------------------------------------------------------------------------------- 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
Need amount paid.xls
Description: MS-Excel spreadsheet
