Need help to expand existing formula

[Guest]

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

 

[JulieD]

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

 

[Dave Peterson]

How about:

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

 

[JE McGimpsey]

one way:

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

 

[Guest]

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