Conditional Sum Argument results do not equal cell results Excel

  • Thread starter Randy R Mullins
  • Start date
R

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.
 
T

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
 
R

Randy R Mullins

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top