COUNT or SUM with multiple criteria

[Guest]

Here is my current formula, but it is returning the wrong answer, so
obviously I'm doing something wrong.

I am looking for the number of negative dollars in the range I1:I2500 that
meets the other criteria. Can anyone please help?

=SUMPRODUCT(--($A$1:$A$2500="CLE"),--($B$1:$B$2500="IE"),--($E$1:$E$2500=6),$I$1:$I$2500<0)

 

[Biff]

Hi!

Do you want the count or the sum?

Count:

=SUMPRODUCT(--($A$1:$A$2500="CLE"),--($B$1:$B$2500="IE"),--($E$1:$E$2500=6),--($I$1:$I$2500<0))

Sum:

=SUMPRODUCT(--($A$1:$A$2500="CLE"),--($B$1:$B$2500="IE"),--($E$1:$E$2500=6),--($I$1:$I$2500<0),$I$1:$I$2500)

Biff

 

[Guest]

I want to COUNT the number of values less than zero.

 

[Roger Govier]

Hi Teri

Try making your last part a condition, then using the same range for the values.

=SUMPRODUCT(--($A$1:$A$2500="CLE"),--($B$1:$B$2500="IE"),--($E$1:$E$2500=6),--($I$1:$I$2500<0),$I$1:$I$25000)


Regards

Roger Govier

 

[Guest]

Biff, you RULE!! It worked perfectly!

Hi!

Do you want the count or the sum?

Count:

=SUMPRODUCT(--($A$1:$A$2500="CLE"),--($B$1:$B$2500="IE"),--($E$1:$E$2500=6),--($I$1:$I$2500<0))

Sum:

=SUMPRODUCT(--($A$1:$A$2500="CLE"),--($B$1:$B$2500="IE"),--($E$1:$E$2500=6),--($I$1:$I$2500<0),$I$1:$I$2500)

Biff

 

[Guest]

Why won't this work when I change the formula so the ending argument is
,--($J$1:$J$2500<.20)) ??

I keep getting a #DIV/0 error!

 

[Biff]

Why won't this work when I change the formula so the ending argument is

,--($J$1:$J$2500<.20)) ??

I keep getting a #DIV/0 error!

That Sumproduct formula doesn't do any division so I'll bet you have #DIV/0!
errors in that range - J1:J2500.

Biff

 

[Guest]

Hi Biff,

I just want to say thank you. Searching this site I found you and this
great answer to my question. It was perfect. Thanks and keep the answers
coming