Forum Discussion

JamesWalter5020

Copper Contributor

Mar 06, 2024

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.

Formulas and Functions

OliverScheurich
Mar 06, 2024
JamesWalter5020
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)))

mathetes
Silver Contributor
Mar 06, 2024
JamesWalter5020

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)
CountWeekDaysWithYes.xlsx10 KB
OliverScheurich
Gold Contributor
Mar 06, 2024
JamesWalter5020
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)))
- JamesWalter5020
  Copper Contributor
  Mar 06, 2024
  Thank you! This works exactly as intended!

Resources