Forum Discussion
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 cells because there is a lot of movement happening in that column and want to ensure the formula doesn't break. Does anyone know of some example code or what type of equation can do this?
=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 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))
