Need help to expand existing formula
 

I have this formula, which works wonderfully, but I want to expand it:
=COUNTIF(L30:L500,"=-4")-COUNTIF(L30:L500,"0")
I want that formula to count only if "BAG" is in E30:E500.

Is this possible?

Connie

Hi Connie

you'll need to use the SUMPRODUCT formula if you want to count the number of
records meeting two criteria:

=SUMPRODUCT(--(L30:L500=-4),--(E30:E500="BAG"))-SUMPRODUCT(--(L30:L500=0),--(E30:E500="BAG"))

Check out
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

for details on how the SUMPRODUCT function works.

cheers
JulieD

"Connie Martin" wrote in message
...
I have this formula, which works wonderfully, but I want to expand it:
=COUNTIF(L30:L500,"=-4")-COUNTIF(L30:L500,"0")
I want that formula to count only if "BAG" is in E30:E500.

Is this possible?

Connie

How about:

=SUMPRODUCT(--(E30:E500="BAG"),--(L30:L500=-4),--(L30:L500<=0))



Connie Martin wrote:

I have this formula, which works wonderfully, but I want to expand it:
=COUNTIF(L30:L500,"=-4")-COUNTIF(L30:L500,"0")
I want that formula to count only if "BAG" is in E30:E500.

Is this possible?

Connie

--

Dave Peterson

one way:

=SUMPRODUCT(--(L30:L500=-4),--(L30:L500<=0),--(E30:E500="BAG"))


In article ,
"Connie Martin" wrote:

I have this formula, which works wonderfully, but I want to expand it:
=COUNTIF(L30:L500,"=-4")-COUNTIF(L30:L500,"0")
I want that formula to count only if "BAG" is in E30:E500.

Is this possible?

Connie

Thank you, Dave, Julie and JE. I've seen this SUMPRODUCT before! I will
have to learn it because this has come up before. Thank you for your
responses. What a wonderful newsgroup! Have a great Friday! Connie


"Connie Martin" wrote:

I have this formula, which works wonderfully, but I want to expand it:
=COUNTIF(L30:L500,"=-4")-COUNTIF(L30:L500,"0")
I want that formula to count only if "BAG" is in E30:E500.

Is this possible?

Connie