Count If statement - refer to label name on separate sheet

[Emilia_Emcke]

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?

[#2 · Accepted solution]

@Emilia_Emcke 

=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.

[#3]

@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

[#4]

@Emilia_Emcke 

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))

[#5]

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?

[#6]

@Emilia_Emcke 

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

An alternative could be SUMPRODUCT.

[#7]

@Emilia_Emcke 

=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.

 

[#8]

@Emilia_Emcke 

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))

[#9]

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

[#10]

@Emilia_Emcke 

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

[#11]

Thank you so much for your help @Hans Vogelaar 

[#12]

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

[#13]

@Emilia_Emcke 

See my reply to your private message.

[#14]

@Emilia_Emcke 

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.