Conditional Sum Argument results do not equal cell results Excel

[Randy R Mullins]

I am using a consitional sum with three variables =SUM(IF('Current Period
NSA'!$D$8:$D$3000=A23,IF('Current Period NSA'!$E$8:$E$3000=B23,IF('Current
Period NSA'!$H$8:$H$3000=C23,'Current Period NSA'!$AB$8:$AB$3000,0),0),0))
The argument itself shows the correct answer when I edit the formula but when
I close the argument window the cell shows a value of 0.

 

[Tom Hutchins]

Your formula works when I try it. It sums the value in column AB (in rows 8
through 3000) for every row in which column D = cell A23, column D = cell
B23, and column H = cell C23. For example, if D10 = A23 and D10 = B23 and
H10 = C23, the formula retrieves the value from AB10. Is this what you
intended?

Although you do not mention it, this is an array formula, which must be
entered by pressing Ctrl+Shift+Enter together.

Hope this helps,

Hutch

 

[Randy R Mullins]

Thanks the array piece is what eluded me - have not had much reason to use
them.
Thanks again

 

[Tom Hutchins]

Thanks for the feedback. BTW, the following SUMPRODUCT formula gives the same
results as the array formula:

=SUMPRODUCT(--($D$8:$D$3000=A23),--($E$8:$E$3000=B23),--($H$8:$H$3000=C23),($AB$8:$AB$3000))

Regards,

Hutch