So, I have a spreadsheet with data from a survey. There are 375 respondents (rows) and 26 questions (columns). I use COUNTIF in order to figure out the frequency of responses and SUMPRODUCT to do some finer analysis.
Here's my problem, some of the questions have multiple valid responses. In those cases the data was entered into the same cell. So I have a column that looks like this:
1,4
2
1
3,12
1,11
8,
3,14,9
And so on....
I can't figure out how to count the number of "1"s. It would be much easier if the responses could only be 1-9, but unfortunately they can be 1-20.
So far, I've come up with:
=SUM(IF(ISNUMBER(FIND("1",A1:A375)),1,0))
But obviously that doesn't work or I wouldn't be here.
For the above example it returns 5, when the answer should be 3
Any ideas? Thanks in advance!
Bookmarks