How to sum values in a column based on day of the week in Google sheets

I have a Spreadsheet with duration values in one column, and date values in another.

I want to sum duration values based on a particular day of the week... eg, in the example screenshot, the first and last dates are Friday. So I want a formula that would add the duration values from the corresponding cells... a total of 17:00

I've tried a formula like this. But this doesn't work.

=SUMIF(D:D, CHOOSE(WEEKDAY(DATE(),2), "Fri") , A:A)

Upvotes: 1

Answers (4)

player0

Reputation: 1

try:

=TEXT(ARRAYFORMULA(SUMIF(WEEKDAY(A:A, 11), 5, D:D)), "[h]:mm")

Upvotes: 2

ADW

Reputation: 4247

You could try:

=sumproduct(weekday(A:A)=6,D:D)

Upvotes: 3

Aerials

Reputation: 4419

One approach to do this is the following:

In column E put the weekday values. Use WEEKDAY() function which depending on your setting will make Friday any integer between 1-7. If using default Friday will be "6".
Use the SUMIF() function. Eg.=sumif(E:E,"=6",D:D)
Make sure the cell where your SUMIF() formula resides, also has a duration number format.

Upvotes: 0

Foxfire And Burns And Burns

Reputation: 11978

Not sure if there is an easy way because I'm noob at Google Sheets, but try with array formula:

=ArrayFormula(SUMIF(D1:D3;WEEKDAY(A1:A3;2);5))

Using it as array formula, I got this:

Hope this helps

Upvotes: 0

How to sum values in a column based on day of the week in Google sheets

Answers (4)

One approach to do this is the following:

Related Questions