![]() |
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) |
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) |
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) |
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) |
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) |
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) |
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) |
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