What WEEKDAY Returns
A bank statement gives you a date for every transaction, but not whether that date was a Saturday. Excel can work that out for you. Microsoft documents WEEKDAY as returning the day of the week for a date as an integer, with the syntax WEEKDAY(serial_number, [return_type]).
The return_type argument controls the numbering. Omitted or 1 gives 1 (Sunday) through 7 (Saturday). Using 2 gives 1 (Monday) through 7 (Sunday), which is convenient here: with return_type 2, Saturday is 6 and Sunday is 7, so a weekend day is any result of 6 or more. Microsoft also lists return_type 3 (0 for Monday to 6 for Sunday) and 11 to 17, where the week starts on a chosen day.
Microsoft notes that dates entered as text can cause problems for WEEKDAY, and that a serial_number outside the current date base returns a #NUM! error. If a date column was pasted in as text, fix it before building formulas on top of it.
Set Up the Sample Data and a Helper Column
Put the transaction dates in column A and the spending amounts in column B, with headers in row 1 and data in rows 2 to 9. The figures below are invented. Column C is a helper column that holds the weekday number for each row, using =WEEKDAY(A2,2) in C2 and filled down to C9.
Sample data (date, amount, and the value WEEKDAY returns with return_type 2):
2026-09-04 30.00 5 (Friday)
2026-09-05 45.50 6 (Saturday)
2026-09-06 22.00 7 (Sunday)
2026-09-07 18.25 1 (Monday)
2026-09-09 12.75 3 (Wednesday)
2026-09-12 60.00 6 (Saturday)
2026-09-13 35.00 7 (Sunday)
2026-09-14 27.50 1 (Monday)The eight amounts add up to 251.00. Keeping the helper column visible makes it easy to spot-check a few rows against a calendar before you trust the totals.
Total Each Group with SUMIFS
Microsoft documents SUMIFS as SUMIFS(sum_range, criteria_range1, criteria1, ...), where each criteria range must have the same number of rows and columns as sum_range, and comparison operators such as ">32" are written in quotation marks. Applied to the helper column:
=SUMIFS(B2:B9, C2:C9, ">=6") -> 162.50 (weekend)
=SUMIFS(B2:B9, C2:C9, "<=5") -> 88.50 (weekday)The weekend rows are 45.50, 22.00, 60.00 and 35.00, which total 162.50. The weekday rows are 30.00, 18.25, 12.75 and 27.50, which total 88.50. The two results add back to 251.00, a useful check that every row landed in exactly one group.
Skip the Helper Column with SUMPRODUCT
Microsoft documents SUMPRODUCT as =SUMPRODUCT(array1, [array2], ...), where arrays must have the same dimensions or the function returns #VALUE!, and its conditional example multiplies logical tests together, such as =SUMPRODUCT((B2:B9=B12)*(C2:C9=C12)*D2:D9). Each test returns 1 for a match and 0 otherwise, so only matching rows contribute.
Following that same pattern, a weekend total without a helper column can be written as shown below. The Microsoft page does not show WEEKDAY inside SUMPRODUCT, so test this form against the SUMIFS result on your own data before relying on it.
=SUMPRODUCT((WEEKDAY(A2:A9,2)>=6)*B2:B9)On the sample data this should return 162.50, matching the SUMIFS weekend total. If it does not, check that the dates are real dates rather than text.
Getting the Statement Into Excel First
These formulas need real dates and numeric amounts in clean rows. If your transactions are still locked inside a PDF, converting them first saves retyping. PDFtoRow converts bank statement PDFs to Excel or CSV and offers a free preview of the first 10 rows.
Convert Bank Statement PDFs Cleanly with PDFtoRow
Tired of fixing broken columns, corrupted dates, and numbers stored as text? PDFtoRow extracts transaction tables directly from digital PDF vector layers, preserving check numbers and outputting true numeric amounts. Statements up to 5 pages are free once per email every 24 hours. Check the 10-row preview before you download.
Frequently Asked Questions
With return_type 2, Microsoft documents the numbering as 1 (Monday) through 7 (Sunday), so Saturday is 6 and Sunday is 7 and a weekend day is any result of 6 or more.
Microsoft states that a serial_number outside the current date base, or a return_type outside the supported values, returns a #NUM! error.
Yes. Microsoft states that each criteria range must have the same number of rows and columns as the sum_range.
Primary Evidence & Official References
- WEEKDAY function - Microsoft Support — support.microsoft.com (Verified 2026-10-29). Official source documentation →
- SUMIFS function - Microsoft Support — support.microsoft.com (Verified 2026-10-29). Official source documentation →
- SUMPRODUCT function - Microsoft Support — support.microsoft.com (Verified 2026-10-29). Official source documentation →