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]

Hi,

Welcome to the forum.

If you have one line per individual (so 400), and want to know how many have put 7 in column BI and 3 in column AX, you can use

=contifs(BI:BI,7,AX:AX,3)

or you can count the "yes"

=countif(column where you have your formula:column where you have your formul,"yes")

 

[Runner10]

Thank you! I tried as you suggested and it works!

Now, I have just 1 follow up question:
So right now, I have this: =COUNTIFS(A16:A417,1,B16:B417,7)
This tells me of the people who selected choice 1 in column A, how many of them also selected choice 7 in column B.

Now I also need to know, of the people who selected choice 1 in column A, how many of them selected choices 1-6 (all combined together) in column B. Or another way of saying this is I need to find out how many people who selected choice A in column 1 did NOT select choice 7 in column B.
I tried this, but it's off: =COUNTIFS(A16:A417,1,B16:B417,"=<6")

Can you please help me with this too? THANK YOU!

 

[Aladin Akyurek]

Thank you! I tried as you suggested and it works!

Now, I have just 1 follow up question:
So right now, I have this: =COUNTIFS(A16:A417,1,B16:B417,7)
This tells me of the people who selected choice 1 in column A, how many of them also selected choice 7 in column B.

Now I also need to know, of the people who selected choice 1 in column A, how many of them selected choices 1-6 (all combined together) in column B. Or another way of saying this is I need to find out how many people who selected choice A in column 1 did NOT select choice 7 in column B.
I tried this, but it's off: =COUNTIFS(A16:A417,1,B16:B417,"=<6")

Can you please help me with this too? THANK YOU!

Try...

=COUNTIFS(A16:A417,1,B16:B417,"<>7")

 

[Runner10]

THANK YOU!
So this formula looks through all cells in column B for how many cells have numbers bigger or less than 7, but not equal to 7, right?

 

[pgc01]

Hi

<>

means "not equal to" or "different from"

 

[Aladin Akyurek]

THANK YOU!

You are welcome.

So this formula looks through all cells in column B for how many cells have numbers bigger or less than 7, but not equal to 7, right?

See...

Hi

<>

means "not equal to" or "different from"

 

[Runner10]

Another follow up question, for the equation below how can I adjust it so that answer choices 2, 4 and 7 are excluded from the count, instead of just excluding 7?

=COUNTIFS(A16:A417,1,B16:B417,"<>7")

thank you.

 

[Aladin Akyurek]

Another follow up question, for the equation below how can I adjust it so that answer choices 2, 4 and 7 are excluded from the count, instead of just excluding 7?

=COUNTIFS(A16:A417,1,B16:B417,"<>7")

thank you.

=COUNTIFS(A16:A417,1,B16:B417,"<>2",B16:B417,"<>4",B16:B417,"<>7")

which is not general.

A better option is:

=SUMPRODUCT(--(A16:A417=1),1-ISNUMBER(MATCH(B16:B417,{2,4,7},0)))

 

[Runner10]

Thank you!
What do you mean "which is not general"