Counting number of times a value appears if..

jensorr - Jun 30, 2009 at 08:44 AM
sambr22 Posts 1 Registration date Thursday April 21, 2016 Status Member Last seen April 21, 2016 - Apr 21, 2016 at 05:24 AM

I conducted a survey in which some questions allowed more than one response.

Using Excel - I'm trying to count the number of times a certain value appears in a column even if there are multiple values in the same cell.

For example: A respondant may have selected answer a, b, d for question 5 (all 3 of these would be in the same cell), while another respondant may have selected answer a, c, d for the same question. For that entire column, I want to know how many times respondants selected "a" regardless of any other selections made.
COUNTIF only counts this response in that column if "a" was the only option selected.

Please help!

3 responses

mubashir aziz Posts 190 Registration date Sunday April 12, 2009 Status Member Last seen February 16, 2010 165
Jul 1, 2009 at 01:38 AM
Suppose your data is in Column A then paste this formula anywhere or in A12 and Press Ctr+Shift+Enter as this is any Array formula ....... After pressing Ctr+Shift+Enter to enter the formula you will see {} will occur around the formula ={SUM(IF(ISERROR(SEARCH("*a*",A1:A10,1)),0,1))} ... never try to enter {} manually ....

Array Formula : After entering the formula Press Ctrl+Shift+Enter