Need help with SUM(IF (Excel 97)

Topic

New Reply

I need a SUM(IF formula that looks at 2 different sets of criteria in order to display a result. I have seen this formula in other spread sheets, but cannot seem to get it to work in my sheet.

If the date on Sheet2 matches the date on Sheet1, and the value of Sheet2, Column B = 8, then sum column C. I have seen this displayed like {SUM(IF((rdate=pdate)*(inserter=”8″),one))}

What am I doing wrong?

Reply | Quote

Replies

How do you want to compare the rdate and pdate ranges? The pdate range contains 30 dates in ascending order, and rdate contains 43 dates in descending order.

Reply | Quote

Sorry for the lack of clarity.

If any of the dates in the rdate column are a match for G3 (in the pdate column, Sheet1). Does this help?

Reply | Quote

Sorry, I don’t understand. G3 cannot match any date. Please try to state EXACTLY what you want, perhaps by providing a sample calculation of what the result should be.

Reply | Quote

Typo, meant cell A3. I have re-attached the spreadsheet with part of the formula in Cell G3 of Sheet1. I need to add additional criteria to this formula. The result should be 40,584…which is a match for for the date of 06/01/2004, and inserter 8 (from Sheet2, column .

Reply | Quote

Sorry, I don’t understand. G3 cannot match any date. Please try to state EXACTLY what you want, perhaps by providing a sample calculation of what the result should be.

Reply | Quote

Sorry for the lack of clarity.

If any of the dates in the rdate column are a match for G3 (in the pdate column, Sheet1). Does this help?

Reply | Quote

How do you want to compare the rdate and pdate ranges? The pdate range contains 30 dates in ascending order, and rdate contains 43 dates in descending order.

Reply | Quote

In cell G3 place this formula.

=SUM(IF((Sheet2!$A$2:$A$44=A3)*(Sheet2!$B$2:$B$44=”8″),Sheet2!$C$2:$C$44))

use Ctrl-Shift-Enter to place it because it is an array formula. Copy it down and that should do it for you.

yoyoPHIL

Reply | Quote

Thanks, this is exactly what I needed. Any ideas why it would not work with defined column names?

Reply | Quote

It would work if you defined inserter as Sheet2!B2:B44 etc.

Reply | Quote

It would work if you defined inserter as Sheet2!B2:B44 etc.

Reply | Quote

Thanks, this is exactly what I needed. Any ideas why it would not work with defined column names?

Reply | Quote