ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   COUNT or SUM with multiple criteria (https://www.excelbanter.com/excel-worksheet-functions/57657-count-sum-multiple-criteria.html)

Teri

COUNT or SUM with multiple criteria
 
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

COUNT or SUM with multiple criteria
 
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

"Teri" wrote in message
...
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)




Teri

COUNT or SUM with multiple criteria
 
I want to COUNT the number of values less than zero.

"Biff" wrote:

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

"Teri" wrote in message
...
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)





Roger Govier

COUNT or SUM with multiple criteria
 
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


Teri wrote:
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)


Teri

COUNT or SUM with multiple criteria
 
Biff, you RULE!! It worked perfectly!

"Biff" wrote:

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

"Teri" wrote in message
...
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)





Teri

COUNT or SUM with multiple criteria
 
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" wrote:

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

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

COUNT or SUM with multiple criteria
 
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

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

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

"Teri" wrote in message
...
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)







MoonBlosm

COUNT or SUM with multiple criteria
 
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 :)

"Biff" wrote:

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

"Teri" wrote in message
...
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)






All times are GMT +1. The time now is 08:24 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com