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