How to 'count' only when header matches value?

user_10719861 0
Viewed 118

I have a Google Form that collects a bunch of data from dropdown questions on a Sheet with each question going to one column (as normal). On separate sheets, I want to be able to count how many times each option is selected. Here is an example of what the response sheet might look like. A, B, and C are all questions.

Input Sample

I would then have separate sheets for 'Person?', 'Place?', and 'Thing?'. The 'Person?' sheet would look something like this:

Person sheet

I want to be able to add in the count of each time the option appears for that question. In the example, notice that 'Napoleon" is in both Col A and Col C. If I just count the number of times 'Napoleon' appears, I will get '2' even though he only appears once in the "Person?" responses.

I originally used a QUERY function like =QUERY('Input Data'!1:1000, "select count(A) where A contains '"&$A2&"'",0). BUT, I need it to be dynamic. So the "Person?" question may not always be Col A. I want the Query (or whatever formula) to search the headers and only return the count of that option for that question even if the column location changes.

google-sheets-formula google-sheets
2 Answers

Okay, I figured it out! In case someone else is curious, I used this formula:

=QUERY({'Input Data'!A1:L}, "SELECT COUNT(Col"&MATCH("Person?", 'Input Data'!1:1,0)&") WHERE Col"&MATCH("Person?", 'Input Data'!1:1,0)&" CONTAINS '"&$A2&"' label COUNT(Col"&MATCH("Person?", 'Input Data'!1:1,0)&") ''",0)
user_107198610

Lee, I sent you a PM about your most recent post, but in the process, I came across this one. There is no need for multiple formulas or manual entry references. One formula can produce the entire report with headers, listing and counts:

=IFERROR(QUERY(FILTER(FILTER(A:L,A:A<>""),A1:L1="Person?"),"Select Col1, COUNT(Col1) GROUP BY Col1 ORDER BY Col1 LABEL COUNT(Col1) 'Count'",1),"No Matches")

Just fill in the header your looking for between the quotes where Person? is now.

The double FILTERs mean "Start with only rows where Col A is not null and Row 1 reads 'Person?'"

Then QUERY simply returns the unique names in the left column and their counts in the right column. Because the QUERY had a final parameter of 1, any existing header will be kept (in this case, the one you were searching for); and the created column will receive a header (i.e., LABEL) of Count.

IFERROR will give a friendly error message if no matches are found (in which case check that what you entered for the search in the formula exactly matches a column header in the range).

user_139032830
Related