I have pre and post survey data for a classes my company runs that I want to process on a google sheet. Those surveyed are asked a question and have a set number of responses. What I have been doing is using a COUNTIF, going to the column that contains my question, and counting the occurrences of various responses. But I see a problem in the future when surveys are changed, adding any columns to the sheet that stores the data ruins this method. I want a COUNTIFS (I think this is the best function for this) that searches for the column that contains my question, counts the occurrences of whatever response I'm looking for, and returns that number.
I've attached a mockup of the actual spreadsheet. Some of the values are changed but the cell references are the same. 'Table Data'! 'Pre Survey Data'!
=COUNTIFS(Substitute(Address(2,11,4,,"Pre Survey Data"),"2","") & ":" & "K", B32)
This is the formula I've got so far. B32 contains the response I'm trying to count within the column I want to search. "11" is a place holder because I know what column contains my question. "& ":" & "K"" is another placeholder, it is supposed to concatenate with SUBSTITUTE() to make the range "'Pre Survey Data'!K:K". It does that successfully but Sheets doesn't read what is returned as a range.
Am I using the right formula?
Am I misunderstanding how formulas in sheets/excel work?
Please help :(
CodePudding user response：
I've looked at your linked sheet. Your question as illustrated in 'Table Data' Row 32 represents something that has virtually nothing in common with the data and layout above it, which contains merged cells, answers that are not exact within the form responses, responses such as "Not Listed" and "Null" which are not clear as to whether those are literal response labels or not, other answer choices that contain parenthetical score values — and none of the questions or answers for which are reflected in your sheet.
That said, I will answer the specific (and seemingly unrelated) question you've asked given the Row-32 information, your note, and nothing else.
Delete 'Table Data'!C32 and replace it with the following:
=ArrayFormula(COUNTA(QUERY(FILTER(FILTER('Pre Survey Data'!A:Z,'Pre Survey Data'!A:A<>"",ROW('Pre Survey Data'!A:A)>1),'Pre Survey Data'!1:1=A32),"Select * WHERE Col1 = '"&B32&"'")))
The data is FILTERed twice: once to get all populated rows, and once to limit to only the column which has the exact string (spaces, punctuation and all) listed in A32, and only data from Row 2 down.
QUERY then rules out all entries in that column that are not an exact match for the string in B32.
COUNTA counts the remaining results of the QUERY.