COUNTIF cells meet one criteria or another, but not both

[whaden]

Hi,
I have a survey question in which the answers are 4, 5, 6 OR All of the above. Some of the respondents replied 4,5,or 6 but also replied All of the above. I want to count the response if the respondents replied 4,5,6 OR if they replied All of the above, but not both. The way I have it set up now is =COUNTIF(AK1085,"4")+COUNTIF(AK1085,"5")+COUNTIF(AK1085,"6")+COUNTIF(AD1085,"All of the above") but that is counting as 2 because the response fulfills both criteria. How do I set up the formula to count only once if the response is 4,5, or 6 OR All of the above?

Thank you for your help!

[chullan88]

Try
=SUMPRODUCT(COUNTIF(AK1085,{4,5,6,"All of the above"}))

[Sam Capricci]

I'm having trouble understanding your request. You don't need a countif for one cell. You could use an if statement for that. Something like
=IF(AD1085="all of the above",1,IF(OR(AK1085=4,AK1085=5,AK1085=6),1,""))

[whaden]

Sorry, I did not clarify, All of the above is in a different cell (AD1085). Is there a way to count if AK1085 equals 4, 5, or 6 OR if AD1085 equals All of the above? But again, if both criteria are true I only want it to calculate once.

[whaden]

I'm having trouble understanding your request. You don't need a countif for one cell. You could use an if statement for that. Something like
=IF(AD1085="all of the above",1,IF(OR(AK1085=4,AK1085=5,AK1085=6),1,""))

This seems to work. Thank you!

[chullan88]

I think Sambo kid's formula can be applied just change the cell reference accordingly
If its not working Can you post a sample of your data

[Sam Capricci]

this would also be a shorter version of what I gave you... =IF(AD1085="all of the above",1,IF(OR(AK1085,{4,5,6}),1,""))

[AlKey]

Or this

Please Login or Register  to view this content.

[Sam Capricci]

AlKey, the problem is "all of the above" and 4, 5 and 6 are not located in the same cells, AD1085 and AK1085 respectively.

[AlKey]

AlKey, the problem is "all of the above" and 4, 5 and 6 are not located in the same cells, AD1085 and AK1085 respectively.

Thanks Sam! I didn't realize that there were two different cells. Sorry about that.

[Sam Capricci]

Thanks Sam! I didn't realize that there were two different cells. Sorry about that.

nothing to apologize about, just wanted to point it out as to why mine was different.

[AlKey]

But I think it should be = not comma

Please Login or Register  to view this content.

[Sam Capricci]

But I think it should be = not comma

Ha Ha, YOU are right, thanks for the correction.

[AlKey]

And this one a bit shorter by avoiding IF(OR

Please Login or Register  to view this content.