Previous Topic Next Topic

Counting number of times a value appears if..

Solved/Closed
Excel
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
Hello,

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!
Thanks
Related:

3 replies

Answer 1 / 3
mubashir aziz Posts 190 Registration date Sunday April 12, 2009 Status Member Last seen February 16, 2010 160
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 
=SUM(IF(ISERROR(SEARCH("*a*",A1:A10,1)),0,1))
3
Answer 2 / 3
Artemis
Apr 28, 2010 at 12:47 PM
The array formula works, but it takes too much computer power to calculate and will make the workbook larger in size. Use the COUNTIF formula with the conjunction of the wild card ("*").

IF CELL B1, contains the following values: a, b, c, d, e, f, g
and cell B2, contains the following values: a, b, c, d, e, f, g

You want to know how many times the value "a" shows up in column B across all the cells. The formula to use would be:

=COUNTIF($B:$B,"*"&"a"&"*")

the formula should return quantity of 2, since a is showes up in cell B1 and B2.
1
anon
Apr 29, 2010 at 01:55 PM
this works perfectly!
0
Mr Excel
Aug 4, 2010 at 12:13 PM
How do you do it with a cell reference instead of a value. i.e A10 instead of "a"?
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Aug 5, 2010 at 07:41 AM
Mr Excel: Like this
=COUNTIF($B:$B,"*"&A10 &"*")
0
sambr22 Posts 1 Registration date Thursday April 21, 2016 Status Member Last seen April 21, 2016
Apr 21, 2016 at 05:24 AM
Hi, if I have a column of values from an open question on a survey, how can I see:
- what words were said
- how many times each word were said

For instance, a participant may say that they like a TV because it is: cool, smart, useful.
0
Answer 3 / 3
stuzzy
Sep 23, 2009 at 06:46 AM
doesnt work for me. says thers none of the data im looking for in the cells im searching when there clearly is
-1

On the same themes