sumif vs. sumproduct giving #VALUE!

[MetLife]

Hi,

I usually use the sumproduct function, but in my worksheet it is not working. Sumif works.

#VALUE! =SUMPRODUCT( ('CIFE 831'!$A$10:$A$121173=AG6)*('CIFE 831'!$BS$10:$BS$121173) )
233,838,675 =SUMIF('CIFE 831'!$A$10:$A$121173,AG6,'CIFE 831'!$BS$11:$BS$121173)
667 =SUMPRODUCT( ('CIFE 831'!$A$10:$A$121173=AG6)*1 )

Why does the first formula not return a value but the other two work? Is there some limit to the sumproduct?

Thanks,

 

[RoryA]

Most likely you have some non numeric data in column BS. I don't know why you'd use SP over SUMIF for that situation, but you could amend it to:

=SUMPRODUCT(--('CIFE 831'!$A$10:$A$121173=AG6),'CIFE 831'!$BS$10:$BS$121173)

 

[MetLife]

Most likely you have some non numeric data in column BS. I don't know why you'd use SP over SUMIF for that situation, but you could amend it to:

=SUMPRODUCT(--('CIFE 831'!$A$10:$A$121173=AG6),'CIFE 831'!$BS$10:$BS$121173)

The other problem I noticed is that when I sort the table and add using "Sum" I don't match the sumif formula.

Is this non numeric data issue?

 

[RoryA]

No, both SUM and SUMIF will ignore anything that isn't a number in column BS.

 

[MetLife]

No, both SUM and SUMIF will ignore anything that isn't a number in column BS.

Maybe there is an error in my version of 365. Because I am using the SUMIF and I compare this to the sorted value and manually using SUM. They give different results.

 

[MetLife]

Maybe there is an error in my version of 365. Because I am using the SUMIF and I compare this to the sorted value and manually using SUM. They give different results.

Whoops, I just figured it out.

SUMIF Had a small error in the formula:

=SUMIF('CIFE 831'!$B$10:$B$200000,$AG19,'CIFE 831'!$BT$11:$BT$200000)

should be

=SUMIF('CIFE 831'!$B$11:$B$200000,$AG19,'CIFE 831'!$BT$11:$BT$200000)

Now everything works!