CODEWITHSUNDEEP
excelexcel-formulagoogle-sheets
SJaafar
SJaafar

Reputation: 53

Counting values (countif) on entire columns based on their values

I have a Google Sheet that takes the responses of a Form.

It has two sheets: The Form Responses 1 which contains the responses and the sheet Report which I've created to a) extract the data from the responses and b) format the table.

The sheet Report has two columns: A) the questions and B) the counts of the response Always.

The function I'm using in B2 is:

=COUNTIF('Form Responses 1'!$G:$G,"Always")

What is the easiest way to do something like auto-fill to have this function running for all questions while changing the column only, so the cells of column B will have:

B3  -->     =COUNTIF('Form Responses 1'!$H:$H,"Always")
B4  -->     =COUNTIF('Form Responses 1'!$I:$I,"Always")
B5  -->     =COUNTIF('Form Responses 1'!$J:$J,"Always")
….

Upvotes: 2

Views: 3093

Answers (4)

tigeravatar
tigeravatar

Reputation: 26650

I think you're looking Countif(Index(Match( which would be like this:

=COUNTIF(INDEX('Form Responses 1'!$A:$HZ,0,MATCH(A2,'Form Responses 1'!$1:$1,0)),"Always")

So it will find A2 if it is a header in 'Form Responses 1'!$1:$1 and then get the count of "Always" in that column.

Upvotes: 1

daniel
daniel

Reputation: 648

You need to use indirect() function. Specifically

=countif(indirect("sheet2!c"&row()+5,false),"Always")

where row() returns current row number. So if we are at the second row, we have indirect("sheet2!c7",false), which means the entire 7th column in sheet2, or sheet2!G:G

See full example

Psst... do you know there is webapps.stackexchange.com for all Google Drive questions?

Upvotes: 0

Chris Hick
Chris Hick

Reputation: 3094

Does this formula work as you want:

=COUNTIF(FILTER('Form Responses 1'!A:Z,'Form Responses 1'!1:1=A2),"Always")

Upvotes: 1

user4039065
user4039065

Reputation:

In B2 use,

=COUNTIF(INDEX('Form Responses 1'!$A:$ZZ, 0, ROW(7:7)),"Always")

Fill down.

Upvotes: 3

Related Questions