Can I use multi-condition in countif?

[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

 

[Norman Harker]

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.

 

[Bob Phillips]

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)

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.

 

[Norman Harker]

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.

 

[2rrs]

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

 

[Harlan Grove]

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? ....

....

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))

 

[frank_yang]

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

 

[Norman Harker]

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.

 

[2rrs]

...

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))

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.

 

[frank_yang]

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)