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