# How do I Count if Date is a Saturday

#### Certificates!

##### Member
I have these three columns. I like to sum the totals in column J, but only if the date in culumn H is a Saturday.

The range is H3:H28, I3:I28 and J3:j28

I was thinking along the like of SUMIF((WEEKDAY,(H3:H28),7),J3:J28) but am not getting anywhere.

 DATE (COLUMN H) COMPETITION FORMAT (COLUMN I) ENTRANTS (COLUMN J) 2nd July 2022 Monthly Medal 129 2nd July 2022 Yellow Tee Medal 6 3rd July 2022 Sunday Stableford 38 3rd July 2022 Lady Aitken Cup MT 23 5th July 2022 Tuesday Stableford 111 5th July 2022 Yellow Tee Stableford 19 8th July 2022 Twilight Stableford 11 9th July 2022 Yellow Tee Stableford 3 9th July 2022 President's Weekend R1 154 10th July 2022 President's Weekend R2 44 12th July 2022 Tuesday Stableford 107 12th July 2022 Yellow Tee Stableford 25 16th July 2022 Hall Cup MT 157 17th July 2022 Summer Scramble 80 19th July 2022 Tuesday Stableford 94 19th July 2022 Yellow Tee Stableford 12 22nd July 2022 Twilight Stableford 7 23rd July 2022 Better Ball Stableford 154 24th July 2022 Sunday Stableford 64 26th July 2022 Tuesday Stableford 111 26th July 2022 Yellow Tee Stableford 16 29th July 2022 Twilight Stableford 5 30th July 2022 W H Andrew Trophy MT 159 30th July 2022 Yellow Tee Stableford 3 31st July 2022 Sunday Stableford 26 31st July 2022 Invitation Day 76

#### ETAF

##### Active Member
try
=SUMPRODUCT((WEEKDAY(A3:A28,2)=6)*(A3:A28<>""))

But are your dates - real dates?

or do you want to sum the entrants

=SUMPRODUCT((WEEKDAY(A3:A28,2)=6)*(A3:A28<>"")*(C3:C28))

#### Attachments

• 11 KB Views: 8

#### GraH - Guido

##### Well-Known Member
Maybe this array formula works (it does work with 365):
=SUM((--(TEXT(SUBSTITUTE(H3:H28;MID(H3:H28;FIND(" ";H3:H28)-2;2);"");"ddd")="Sa"))*(J3:J28))
or the above sumproduct, replacing the weekday part with this part (--(TEXT(SUBSTITUTE(H3:H28;MID(H3:H28;FIND(" ";H3:H28)-2;2);"");"ddd")="Sa"))

You might need to confirm the "sum" with CSE for older versions, but I can't test that anymore.

* I'm on EU settings, replace ";" with "," in the formula arguments if needed.

#### Certificates!

##### Member
try
=SUMPRODUCT((WEEKDAY(A3:A28,2)=6)*(A3:A28<>""))

But are your dates - real dates?

or do you want to sum the entrants

=SUMPRODUCT((WEEKDAY(A3:A28,2)=6)*(A3:A28<>"")*(C3:C28))
I do want to sum the entrant and this solves the problem.

Thanks you very much.

you are welcome

#### bosco_yip

##### Excel Ninja
try
=SUMPRODUCT((WEEKDAY(A3:A28,2)=6)*(A3:A28<>""))

But are your dates - real dates?

or do you want to sum the entrants

=SUMPRODUCT((WEEKDAY(A3:A28,2)=6)*(A3:A28<>"")*(C3:C28))
To sum the entrants

ETAF's solution using : =SUMPRODUCT((WEEKDAY(A3:A28,2)=6)*(A3:A28<>""),C3:C28)

But the old day using : =SUMPRODUCT((MOD(A3:A28-2,7)+1=6)*(A3:A28<>""),C3:C28)

They both return the same result

bosco_yip

#### Certificates!

##### Member
Maybe this array formula works (it does work with 365):
=SUM((--(TEXT(SUBSTITUTE(H3:H28;MID(H3:H28;FIND(" ";H3:H28)-2;2);"");"ddd")="Sa"))*(J3:J28))
or the above sumproduct, replacing the weekday part with this part (--(TEXT(SUBSTITUTE(H3:H28;MID(H3:H28;FIND(" ";H3:H28)-2;2);"");"ddd")="Sa"))

You might need to confirm the "sum" with CSE for older versions, but I can't test that anymore.

* I'm on EU settings, replace ";" with "," in the formula arguments if needed.
I did try this one in 365 and replaced the semi-colons with commas, but I got a VALUE error I'm afraid.
To sum the entrants

ETAF's solution using : =SUMPRODUCT((WEEKDAY(A3:A28,2)=6)*(A3:A28<>""),C3:C28)

But the old day using : =SUMPRODUCT((MOD(A3:A28-2,7)+1=6)*(A3:A28<>""),C3:C28)

They both return the same result

bosco_yip
I did try this one, the one you describe as the old day and it goes work fine.

Thanks very much.

#### GraH - Guido

##### Well-Known Member
I did try this one in 365 and replaced the semi-colons with commas, but I got a VALUE error I'm afraid.
I expected as much. Being on Dutch date settings, I had to change the data so it could work with my local settings.
Do consider for next time to upload a sample file, not a pasted table in the thread. We could not know whether or not you had dates or text inside those cells (looking at the formulas working, I think they were dates). Furthermore dates with "st", "nd", "rd" are not so common where I come from.

#### Certificates!

##### Member
I expected as much. Being on Dutch date settings, I had to change the data so it could work with my local settings.
Do consider for next time to upload a sample file, not a pasted table in the thread. We could not know whether or not you had dates or text inside those cells (looking at the formulas working, I think they were dates). Furthermore dates with "st", "nd", "rd" are not so common where I come from.
Goede Daag,

Thanks for the advice. I shall certainly bear it in mind.

Totsiens