Countif Formula w/ Multiple Conditions

  • Thread starter Thread starter 8denise8
  • Start date Start date
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
 
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.
 
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
 
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.
 
Back
Top