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?
