Forum Discussion
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.
=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.
OliverScheurich 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 |
=SUMPRODUCT((B2:B11>3)*(C2:C11="Smith")*(D2:D11>=44562))An alternative could be SUMPRODUCT.
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))
