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)

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)

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)

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)

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)

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)

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)

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)