SUMIFS Issue

[CFitz]

I'm trying to do a sumifs with two criteria. Below is my data and equation.

ID COUNT SUM FIRST 4 LAST 2
010100 1 $40,000.00 0101 00
010101 7 $194,230.00 0101 01
010102 2 $60,000.00 0101 02
010201 4 $120,000.00 0102 01

0101
00

=SUMIFS(B2:B5,D25,A7,E2:E5,"<>"&A8)

A7 = 0101 and A8=00

The IDs are formatted as text (to keep the leading zeroes). The equation is
currently giving me 10 but it should be 9. When I change the second criteria
to equals A8 it works and gives me 1. I've also made IF equations that check
A7 & A8 against the First 4 & Last 2 columns and they behave as they should.
This is only happens in the SUMIFS equation. Any ideas?

Thanks
Chris

 

[Eduardo]

Hi
try
=sumproduct(--($D$2:$D$7=$A$7),--($E$2:$E$7<>$A$8),$B$2:$B$7)

if this helps please click yes, thanks

 

[CFitz]

Yes that worked. I'm not too familiar with the SUMPRODUCT function though I
probably should be.

Does anyone know why the SUMIFS won't work though? Seems like an odd bug
with the function. That criteria works fine on it's own in a SUMIF.

Thanks again!

 

[Eduardo]

Hi,
once you start using it you will not be able to ride off it here you have
more information about sumproduct

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Roger Govier]

Hi

You are correct. It doesn't work.
It seems as though the function doesn't want to deal with a second set of
criteria, when it is text values.

If you change A7 and A8 to 101 and 0, and have numeric values in columns D
and E, then it works fine.

I will take this up with some other colleagues, and see if I can find out
more, and if this is still a problem I will report it as a bug.

 

[Bob Phillips]

I just tried this in 2003

=SUMIF(E2:E5,"<>"&A8,B2:B5)

and it returned 14., not 13 as it should. An array SUM(IF works as does
SUMPRODUCT. So SUMIFS has just carried that forward. I suspected that would
be where the problem was, but I don't know why but I suspect that the
concatenation of <> and 00 is getting <>0, not <>00. Changing E2:E5 to
numeric values seems to confirm this.