Forum Discussion
What formula can I use here?
Hello Excel users!
I am attempting to setup a formula that will allow me to tally based off of 2 cells. As simplified as I can make the statement, would be as follows:
If D2=Yes, determine that the day of the week is Friday and add 1 to G7.
Carry this concept across all days of the week, adjust tally cell to correctly labeled cells.
Ive tried Countif, Countifs, and Vlookup. Either I have my formula inputed incorrectly or I am not using them appropriately. Can anyone help here?
PS: Column B is reference data from other workbooks, C:E are formulaic in nature based off of B.
This formula is in cell F5 and filled across range F5:I5:
=SUMPRODUCT(($D$2:$D$13="Yes")*(WEEKDAY($A$2:$A$13,1)=COLUMN(A$1)))
This formula is in cell F7 and filled across range F7:H7:
=SUMPRODUCT(($D$2:$D$13="Yes")*(WEEKDAY($A$2:$A$13,1)=COLUMN(E$1)))
This formula is in cell F5 and filled across range F5:I5:
=SUMPRODUCT(($D$2:$D$13="Yes")*(WEEKDAY($A$2:$A$13,1)=COLUMN(A$1)))
This formula is in cell F7 and filled across range F7:H7:
=SUMPRODUCT(($D$2:$D$13="Yes")*(WEEKDAY($A$2:$A$13,1)=COLUMN(E$1)))
- Thank you! This works exactly as intended!
I've attached a very simple replica of portions of the sheet you show in your image. I stretched out the days of the week into a single row, so as to more easily copy a formula, but you could modify it to fit your arrangement.
The formula is this
=COUNT(FILTER($A$3:$D$27,(WEEKDAY($A$3:$A$27)=F$1)*($D$3:$D$27="Yes")))There could be other formulas that would work as well. (One of the intriguing aspects of Excel, often visible in this forum, is the variety of methods possible to achieve the same results)
