ExcelBanter - SUMIFS Issue

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - SUMIFS Issue (https://www.excelbanter.com/excel-worksheet-functions/232234-sumifs-issue.html)

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,D2:D5,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

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" wrote:

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,D2:D5,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

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" wrote:

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" wrote:

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,D2:D5,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

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

"CFitz" wrote:

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" wrote:

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" wrote:

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,D2:D5,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

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.
--
Regards
Roger Govier

"CFitz" wrote in message
...
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" wrote:

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" wrote:

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,D2:D5,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

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.

--
__________________________________
HTH

Bob

"CFitz" wrote in message
...
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" wrote:

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" wrote:

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,D2:D5,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