Forum Discussion
Solved
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 cel...
=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.
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))
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?
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))
- Is this returning the average of those scores? Would you not have to include some average calculation?
Sorry, I meant AVERAGEIFS instead of COUNTIFS. I have edited my previous reply.
=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.