Using countif with an and function

  • Thread starter Thread starter Jambruins
  • Start date Start date
J

Jambruins

Column C has a W or and E in it while column D has a number. I want to
count all the cells that have an E in the C column as long as its
corresponding column D number is <-1. How do I do this?
 
try
=sumproduct((e2:e22="e")*(d2:d22<-1))

=sumproduct((e2:e22={"c","w"})*(d2:d22<-1)) for both
 
I do not want to add the numbers in the D column I just want to coun
how many there are. I need to use the countif function with an an
function I believe
 
Hi
as COUNIF only accepts one condition you have to use SUMPRODUCT. The
formula
=SUMPRODUCT((C2:C9999="E")*(D2:D9999<-1))
(as well as the formulas provided by Peo and Don) will just count. To
explain this:
C2:C9999="E" will either return TRUE or FALSE. The same is true for
D2:D9999<-1
SUMPRODUCT will now multiply these results row by row and add the
individual row results. So it will do something like:
TRUE*TRUE+
FALSE*TRUE+
TRUE*FALSE+
FALSE*FALSE
......
Due to this mathematical operation Excel will automatical convert these
boolean values to numbers (TRUE = 1, FALSE = 0). So the above will be
converted to
1*1+
0*1+
1*0+
0*0
=1

Peo for examples used the operator '--' to force the conversion (-- is
equal to (-1)*(-1))

If you want to add the numbers in column D the SUMPRODUCT formula would
look like
=SUMPRODUCT((C2:C9999="E")*(D2:D9999<-1),(D2:D9999))
or
=SUMPRODUCT((C2:C9999="E")*(D2:D9999<-1)*(D2:D9999))

HTH
Frank
 
thanks for the help. It is working except for it is counting the cell
I haven't entered a number in for. I am having it look at cells C2 t
C42. I have a number in cells D2 to D30 so it is counting them but i
is also counting cells D31 to D42 even though there is no number i
them. This is because my formula says count if there is an E in the
column and a number >-1 in the D column. So even though there is n
number in the cell it is saying it is greater than -1, which i
correct. However, I don't want it to count a cell if there isn't
number there. I can't say >0 because some cells have a zero in them.
Any ideas
 
I know what I need to use I just don't know how to enter it into excel.
That is why I ask the question
 
Back
Top