Conditional Sum Help

[techiemom60]

Hi All,

I have 2 sheets in one workbook.

1st sheet - March 18, contains:

# # # OF ACCTS

1 5 10
1 5 15
1 5 20
4 10 500
4 15 1000

I want to, on sheet 2, sum the # of accounts Column D, when Column A
1 and Column C=5. Column B has stuff I don't need at this point.

I have this now, but it is returning only a zero.

=SUM(IF('March 18'!$A$3:$A$10000="1",IF('Marc
18'!$C$3:$C$10000="16",'March 18'!$D$3:$D$10000,0),0))

Any assistance would be greatly appreciated

 

[Pete_UK]

You say you want to sum when column C = 5, but your formula is checking
for Column C = 16. Also, you shouldn't have the values in quotes,
unless they are text values. Anyway, here's a slight alternative to
what you have:

=SUM(IF(('March 18'!$A$3:$A$10000=1)*('March
18'!$C$3:$C$10000=5),'March 18'!$D$3:$D$10000,0))

This is an array formula (as is the one you have already), so when you
type it in or edit it you need to use CTRL-SHIFT-ENTER at the same time
rather than just ENTER. If you do it right then Excel will wrap curly
braces { } around the formula - you should not type these yourself.

Hope this helps.

Pete

 

[techiemom60]

Thank you so much. I was not aware of the CTRL-Shift-Enter. That di
it

 

[Pete_UK]

Glad to be of help - thanks for feeding back.

Pete