Can I use multi-condition in countif?

  • Thread starter Thread starter frank_yang
  • Start date Start date
F

frank_yang

I need count the num of rows which colum C<=29 and colum E>10.(for
example)

Can I use multi-condition in countif?
Or is there any other ways to achieve this?

Thanks
 
Hi Frank!

COUNTIF can't handle multiple conditions.

Try for example:

=SUMPRODUCT(($C$1:$C$100<=29)*($E$1:$E$100>10))

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Frank,

Note that SUMPRODUCT cannot take a whole column as COUNTIF can. As Norman
shows, you have to specify the range to test.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Norman Harker said:
Hi Frank!

COUNTIF can't handle multiple conditions.

Try for example:

=SUMPRODUCT(($C$1:$C$100<=29)*($E$1:$E$100>10))

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Click to expand...
 
Hi Bob!

Thanks for pointing that out. I should have said that can't use whole
columns. I wonder why? Just is! I suppose.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Norman,

I attempted to use the SUMPRODUCT formula and the COUNTIF formula on
the table below with the following results:

=SUMPRODUCT(($F$159:$F166<=29)*(H$159:$H$166>10))…………….......returns 2
=COUNTIF($F$159:$F$166,"<=29")+COUNTIF($H$159:$H$166,">10")….returns
10


F H
159 2 2
160 4 6
161 6 8
162 8 12
163 29 88
164 32 102
165 36 53
166 25 10

10 is the correct answer, do you see any flaws in the setup?

Thanks, 2rrs
 
2rrs said:
I attempted to use the SUMPRODUCT formula and the COUNTIF formula on
the table below with the following results:

=SUMPRODUCT(($F$159:$F166<=29)*(H$159:$H$166>10))............returns 2
=COUNTIF($F$159:$F$166,"<=29")+COUNTIF($H$159:$H$166,">10")..returns 10 ....
10 is the correct answer, do you see any flaws in the setup? ....
Click to expand...
....

What you originally asked for an what you say is the correct answer aren't
the same thing. Rows in which col C <= 29 *AND* E > 10 means rows in which
*BOTH* conditions hold. The COUNTIF formula above doesn't combine the
criteria. It gives the number of rows in col F <= 29 plus the number of rows
in col H > 10, which is neither satisfying criteria col F <= 29 AND col H >
10 nor col F >= 29 OR col H > 10.

To illustrate,

____F__H
1 2 5
2 3 6
3 4 3

=COUNTIF(F1:F3,"<=5")+COUNTIF(H1:H3,">4") returns 5, which is more than the
number of rows. Is this really what you want?

If not, then you need to use SUMPRODUCT (or equivalents).

AND criteria: =SUMPRODUCT((criterion1)*(criterion2))

OR criteria: =SUMPRODUCT(--((criterion1)+(criterion2)>0))
 
Thank you, Norman and Bob:)
I already solved this problem by your help!

BTW, I am so happy to meet an Aussie friend here, I used to stay there
for 1.5 year:)

Cheers Mate,

Frank
 
Hi Frank!

Good to hear its sorted.

What did you leave for?

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Harlan Grove said:
...

What you originally asked for an what you say is the correct answer aren't
the same thing. Rows in which col C <= 29 *AND* E > 10 means rows in which
*BOTH* conditions hold. The COUNTIF formula above doesn't combine the
criteria. It gives the number of rows in col F <= 29 plus the number of rows
in col H > 10, which is neither satisfying criteria col F <= 29 AND col H >
10 nor col F >= 29 OR col H > 10.

To illustrate,

____F__H
1 2 5
2 3 6
3 4 3

=COUNTIF(F1:F3,"<=5")+COUNTIF(H1:H3,">4") returns 5, which is more than the
number of rows. Is this really what you want?

If not, then you need to use SUMPRODUCT (or equivalents).

AND criteria: =SUMPRODUCT((criterion1)*(criterion2))

OR criteria: =SUMPRODUCT(--((criterion1)+(criterion2)>0))
Click to expand...


Ah Ha! I see it now; thank you for your response. I should have seen
it myself. I must learn to read and interpert these responses more
carefully.
 
Hi Norman,
I used to study in Wollongong for my degree in IT. Now I am back home
to continue my career. I will probably go back to Sydney within next
few month after I get my PR:) Maybe we can meet then.

Keep in touch,

Frank

PS, my msn nick name is: laogoat; email: (e-mail address removed)
 
Back
Top