CODEWITHSUNDEEP
excelexcel-2007spreadsheetexcel-formula
anto
anto

Reputation: 325

How to calculate values in a column based on only weekends ( saturday and sunday) in excel?

I have a column 'day' (C6:C37) showing the day of the month and a column 'dailytotal' (F6:F37) showing the total expenditure on each day. I want to calculate total expenditure only on weekends.

How do I calculate the sum of values in 'dailytotal' when Saturday or Sunday is in column 'day'. How can I use SUMIF here for my requirement?

Upvotes: 3

Views: 3273

Answers (2)

Ross Larson
Ross Larson

Reputation: 2437

If you HAVE to use sumif (though Michael's solution works nicely). Here is what you can do.

Add a column (I will refer to as "weekday_range") that takes the =weekday(date, 2) (and fill down column)

THEN you can use

=sumif(weekday_range, ">5", daily_total_range)

you can then hide the column that weekday_range is in if needed

Upvotes: 1

Michael
Michael

Reputation: 507

You can use the WEEKDAY function to determine what day a date falls on. However, there's no way to use that directly with a SUMIF formula. Alternatively, you can use a SUMPRODUCT formula:

=SUMPRODUCT(--(WEEKDAY(C6:C37,2)>5), F6:F37)

Upvotes: 3

Related Questions