SOLVED

# Count If statement - refer to label name on separate sheet

Occasional Contributor

# Count If statement - refer to label name on separate sheet

Hey! I'm trying to create a count if statement that counts all cells in a column on a different sheet with values greater than 4. I need to refer to the column by name, not simply by the array of cells because there is a lot of movement happening in that column and want to ensure the formula doesn't break. Does anyone know of some example code or what type of equation can do this?

13 Replies
best response confirmed by Grahmfs13 (Microsoft)
Solution

# Re: Count If statement - refer to label name on separate sheet

``=SUMPRODUCT((sheet2!\$A\$1:\$I\$1="header 2")*(sheet2!\$A\$2:\$I\$23>4))``

You can try this formula.

This is "sheet2":

The formula returns the number of cells from "sheet2" that are under "header 2" and have a value greater 4.

# Re: Count If statement - refer to label name on separate sheet

@Quadruple_Pawn Hey that is super helpful! Is it also possible to add more conditions. So for example in the table below if I wanted to return the number of scores higher than 3, in smith's class, since the start of 2022, by referring to column labels/names, would that be possible to bake into one formula?

 Score Teacher Date Student 1 3.7 Smith 1/21/2022 Student 2 3.2 Dylan 3/9/2020 Student 3 2 Smtih 10/11/2019 Student 4 3.7 Dylan 5/6/2022 Student 5 2.1 Allen 9/7/2021 Student 6 4.8 Dylan 8/11/2022 Student 7 1.9 Smith 6/29/2022 Student 8 1.8 Allen 8/6/2022 Student 9 2.1 Smith 2/3/2022 Student 10 3.1 Smith 08/0/4/2022

# Re: Count If statement - refer to label name on separate sheet

Try

=COUNTIFS(INDEX(\$A\$2:\$ZZ\$1000, 0, MATCH("Score", \$A\$1:\$ZZ\$1, 0)), ">3", INDEX(\$A\$2:\$ZZ\$1000, 0, MATCH("Teacher", \$A\$1:\$ZZ\$1, 0)), "Smith", INDEX(\$A\$1:\$ZZ\$1000, 0, MATCH("Date", \$A\$1:\$ZZ\$1, 0)), ">="&DATE(2022, 1, 1))

# Re: Count If statement - refer to label name on separate sheet

Wow!!! That worked thank you so much! One last question, would it also be possible to use a similar formula and return the average of the scores, later than 1/1/2022, and continue to refer to column names?

# Re: Count If statement - refer to label name on separate sheet

``=SUMPRODUCT((B2:B11>3)*(C2:C11="Smith")*(D2:D11>=44562))``

An alternative could be SUMPRODUCT.

# Re: Count If statement - refer to label name on separate sheet

``=AVERAGEIFS(\$B\$2:\$B\$11,\$C\$2:\$C\$11,G3,\$D\$2:\$D\$11,">=44562")``

You can try this formula which refers to the unique list of teachers. The formula is in cell H3 and copied down in the example.

# Re: Count If statement - refer to label name on separate sheet

Yes:

=AVERAGEIFS(INDEX(\$A\$2:\$ZZ\$1000, 0, MATCH("Score", \$A\$1:\$ZZ\$1, 0)), INDEX(\$A\$1:\$ZZ\$1000, 0, MATCH("Date", \$A\$1:\$ZZ\$1, 0)), ">="&DATE(2022, 1, 1))

# Re: Count If statement - refer to label name on separate sheet

Is this returning the average of those scores? Would you not have to include some average calculation?

# Re: Count If statement - refer to label name on separate sheet

Sorry, I meant AVERAGEIFS instead of COUNTIFS. I have edited my previous reply.

# Re: Count If statement - refer to label name on separate sheet

Thank you so much for your help @Hans Vogelaar

# Re: Count If statement - refer to label name on separate sheet

Hey Hans, you were such a great help with my last problem and wondering if you could help out again. I have two sheets one sheet with subjects and avg. scores for each year. For each subject on one sheet, I want to index the scores depending on the teacher. So if the row's match across the two different sheets (Math), and the teacher is Smith, then index the score for math in 2022.

Teacher 2019 2020 2021 2022
Math Smith 98 87 67 100
English Allen 81 54 32 78
Spanish Allen 76 85 90 91
Biology Smith 68 67 29 59

# Re: Count If statement - refer to label name on separate sheet

See my reply to your private message.

# Re: Count If statement - refer to label name on separate sheet

See the version attached below.

I had to remove the trailing space from the end of some of the values. "Hardware" and "Hardware " don't match.