COUNTIF

[Connie]

Cell J74 contains this formula:
=COUNTIF(J3:J70,">0")+COUNTIF(J3:J70,"-")

I want to expland it. First I want it to look at column
E. If there is no "BBAG" in column E, then it will
return "0", otherwise it will complete as per formula as
written.

Sounds simple, and likely is, but I can't seem to get my
head around it this Monday afternoon!!

Connie

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--(E3:E70="BBAG"),--((J3:J70>0)+(J3:J70="-")))

 

[Connie]

That works wonderfully! Thank you! Now, I tried to adapt
the same type of formula for another cell and it's not
working. The formula in that cell is even simpler so I
don't understand why it's not working.

Cell J75 has this formula:
=COUNTIF(J3:J30,"<0")

And I want to apply the same conditions---that it looks at
column E first and if there are no "BBAG" in column E,
then it would render 0, otherwise complete the formula as
written.

Using your formula, I put:
=SUMPRODUCT(--(E3:E70="BBAG"),--((J3:J30,"<0"))

Why doesn't that work?

Connie

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--(E3:E70="BBAG"),--(J3:J70<0))
or
=SUMPRODUCT(--(E3:E30="BBAG"),--(J3:J30<0))

Also see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Connie]

Hi,
I'm just getting back to this project. Thank you for your
response. I used the first one, as it more suits my
purposes, and it works great! Thank you!

Connie
Mississauga, Canada