• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How do I Count if Date is a Saturday

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 2022Monthly Medal129
2nd July 2022Yellow Tee Medal6
3rd July 2022Sunday Stableford38
3rd July 2022Lady Aitken Cup MT23
5th July 2022Tuesday Stableford111
5th July 2022Yellow Tee Stableford19
8th July 2022Twilight Stableford11
9th July 2022Yellow Tee Stableford3
9th July 2022President's Weekend R1154
10th July 2022President's Weekend R244
12th July 2022Tuesday Stableford107
12th July 2022Yellow Tee Stableford25
16th July 2022Hall Cup MT157
17th July 2022Summer Scramble80
19th July 2022Tuesday Stableford94
19th July 2022Yellow Tee Stableford12
22nd July 2022Twilight Stableford7
23rd July 2022Better Ball Stableford154
24th July 2022Sunday Stableford64
26th July 2022Tuesday Stableford111
26th July 2022Yellow Tee Stableford16
29th July 2022Twilight Stableford5
30th July 2022W H Andrew Trophy MT159
30th July 2022Yellow Tee Stableford3
31st July 2022Sunday Stableford26
31st July 2022Invitation Day76
 
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

  • count-etaf.xlsx
    11 KB · Views: 8
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.
 
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.
 
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
 
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.
 
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.
 
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
 
Back
Top