How do I Count if Date is a Saturday

[Certificates!]

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.

Thanks in Advance.

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]

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))

 

[GraH - Guido]

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!]

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.

 

[ETAF]

you are welcome

 

[bosco_yip]

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!]

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]

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!]

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