Help on Count If Statement

[calebmichaud]

I am not sure if i am using the correct function here....

but what i am trying to do is count the number of cells in column G
that are greater than 0 AND also have a text value of "C" in column E.
So for instnace G4 would have to be greater than zero and also have an
E4 text value of "C".
Below is the equation i have tried....an array. I keep getting a value
of zero.

=COUNT(IF($G$4:$G$39>"0",IF($E$4:$E$39="C",G4:G39)))


What i am trying to ultimatly do is have this serve as the demoniator
an an equation where i sum values in column G that are greater than
zero and have a corresponding column E value of C. I got this.....and
it works fine.
=SUMIF(E4:E39, "=C", $G$4:$G$39)

I could really use some help.

Thanks.

 

[Pete_UK]

You could do it this way using an array* formula:

=SUM(IF(($G$4:$G$39>0)*($E$4:$E$39="C"),1))

*An array formula needs to be entered using the key combination of
Ctrl-Shift-Enter (CSE) rather than the usual Enter. If you do this
correctly then Excel will add curly braces { } around the formula when
viewed in the formula bar - do not type these yourself. If you amend
the formula you must use CSE again.

Alternatively, you could use this:

=SUMPRODUCT(($G$4:$G$39>0)*($E$4:$E$39="C"))

which does not need to be array-entered.

Hope this helps.

Pete

 

[Alan]

Try this,

=SUMPRODUCT(--(G1:G10>0),--(E1:E10="C"))

Regards,
Alan.

 

[calebmichaud]

Pete

Excellent!! That is a lot of help. i went with the non array because
if i used the array it messes up the fomula i am plugging that portion
into.

Thanks a bunch.

 

[calebmichaud]

I have a follow up question.
Here is the formula i ended up using.
=SUMIF($E$4:$E$39,"=C",$G$4:$G$39)/SUMPRODUCT(($G$4:$G$39>0)*($E$4:$E
$39="C"))

now lets say that in colum E i have "C" but i also have "NE" and "NW"
as options. Can i proceed this with an IF statement. For instance IF
E4 is equal to C it will do the above equation, if it is equal to NE
it will do that equation but with NE subsititued for C.

Caleb

 

[Pete_UK]

Using the value in E4, you could do this:

=SUMIF($E$4:$E$39,$E$4,$G$4:$G$39)/ SUMPRODUCT(($G$4:$G$39>0)*($E$4:$E
$39=$E$4))

Hope this helps.

Pete