COUNTIF formula Help!!!!!!

[Runner10]

I have 2 columns of data. The left column is whether the individual has good or bad behavior. The right column is whether the individual had a positive or negative experience in the emergency room. I have this formula below, which tells me if the one individual had both bad behavior AND a negative emergency room experience.

=IF(AND(BI10=7,AX10=3),"Yes","No")



However, I have over 400 individuals in my study. How can I adjust this formula to tell me how many individuals had both bad behavior AND a negative emergency room experience? I'm thinking I have to add COUNT or COUNTIF into the above formula? But I'm not sure how to set this all up.


Any help would be appreciated. Thank you.

 

[Kamolga]

You might want to work with full colomns in case you add lines later on and add a "0" in the value not to be considered so that if column is empty but A is filled, it does not count it

=SUMPRODUCT(--(A:A=1);1-ISNUMBER(MATCH(B:B;{2;4;7;0};0)))

 

[Aladin Akyurek]

Thank you!

You are welcome.

What do you mean "which is not general"

The Not bit cannot be written in a concise manner. A multitude of negations would become cumbersome to express, while:

=SUMPRODUCT(--(A16:A417=1),1-ISNUMBER(MATCH(B16:B417,List,0)))

where List can contain many items.

 

[pgc01]

Hi

Another option:

=COUNTIF(A16:A417,1)-SUM(COUNTIFS(A16:A417,1,B16:B417,{2,4,7}))

 

[Runner10]

Thank you!

 

[Runner10]

Instead of looking for the nots, would it be easier to modify the formula to look for what I want?
=COUNTIF(A16:A417,1)-SUM(COUNTIFS(A16:A417,1,B16:B417,{2,4,7}))

With the above formula, can I change it to count how many people selected choices 1, 3, 5, 6, 8....instead of counting those who did not select choices 2, 4, 7?

Thanks!

 

[pgc01]

Hi

That is simply:

=SUM(COUNTIFS(A16:A417,1,B16:B417,{1,3,5,6,8}))

 

[Aladin Akyurek]

I'd go for SumProduct with IsNumber/Match.

=SUMPRODUCT(--(A16:A417=1),1-ISNUMBER(MATCH(B16:B417,ExcludeList,0)))

Or, you like costly COUNTIF(S)...

=COUNTIFS(A16:A417,1)-SUMPRODUCT(COUNTIFS(A16:A417,1,B16:B417,ExcludeList))

 

[Runner10]

Thank you!

 

[pgc01]

Or, you like costly COUNTIF(S)...

Hi Aladin

CountIf() and similar are not so costly in the later versions of excel.

MS now even recommends CountIf() like functions instead of SumProduct().

Check the section

Using SUMPRODUCT for Multiple-Condition Array Formulas

in the article

Excel 2010 Performance: Tips for Optimizing Performance Obstructions

https://msdn.microsoft.com/en-us/library/office/ff726673(v=office.14).aspx

Although Sumproduct() is much more flexible, for simple problems like this one I prefer the functions type CountIf() now.

 

[Aladin Akyurek]

Hi Aladin

CountIf() and similar are not so costly in the later versions of excel.

MS now even recommends CountIf() like functions instead of SumProduct().

[...]

I did not claim that SumProduct is faster than CountIf(s), but IsNumber/Match is. And I dare to think that:

1. =SUMPRODUCT(--(A16:A417=1),1-ISNUMBER(MATCH(B16:B417,ExcludeList,0)))
2. =COUNTIFS(A16:A417,1)-SUMPRODUCT(COUNTIFS(A16:A417,1,B16:B417,ExcludeList))

[1] is faster than [2].