Countif Formula w/ Multiple Conditions

8

8denise8

Can someone please help me?

I am trying to create a formula in Excel that will count the number of
rows that contain specific information in 2 columns.

More specifically, if column A:A is titled Regions and K:K is titled
Metric Met, I want to know how many times for the Region Australia,
Metric Met=Yes.

Please let me know. Any information will help.

Thanks,
Denise
 
I

icestationzbra

sumproduct function will not help.

use the following.

=SUM(IF((Sheet1!$A$2:$A$200="Australia")*(Sheet1!$B$2:$B$200="Yes"),1,0))

***after pasting the formula in the formula bar, DO CTRL+SHIFT+ENTER.
***

after CTRL+SHIFT+ENTER, the formula in the formula bar should look
like,

{=SUM(IF((Sheet1!$A$2:$A$200="Australia")*(Sheet1!$B$2:$B$200="Yes"),1,0))}

notice the braces.

do not hit ENTER. this is an array formula, it will not work with
simple ENTER. if you do not see braces, you have not done
CTRL+SHIFT+ENTER.

in place of "Australia", you can even use the cell address, viz, C3.

mac.
 
F

Frank Kabel

That was the same thing I was asking myself. Of course also the SUM
formula is working. So IMO it depends on your personal taste whhich
alternative you prefer
Frank
 
D

Don Guillett

I always prefer, when it will work instead of sum, sumproduct due to not
having to array enter or edit. There has also been some discussion that it
uses less resources than array formulas. Of course, in this case sumproduct
works just fine.
 
Top