CODEWITHSUNDEEP
excel
Tancredii
Tancredii

Reputation: 1

SumIf by Weekday & User

I have a table that I am trying to perform a sumif on, but the catch is I need the formula to filter the data by two range criteria first. Column A = Date, Column D = User, Column H = time to be summed

Basically, I want to be able to sum the time values by user, but then I also need to be able to split this time between weekdays and weekends.

Thus, if user A has 1 session of 0:30 on Monday, Wednesday, Saturday and Sunday, then I want the formula to be capable of summing the 2 hrs such that I can have 1 column in a separate table with the weekday hours, and the column next to this with the summ of all weekend hours.

Any ideas you might have on how I can solve this with a formula rather than redesigning the tables (as they're not mine to redesign) would be most appreciated.

Upvotes: 0

Views: 2045

Answers (1)

user4039065
user4039065

Reputation:

You need to move to a SUMPRODUCT function in order that you can introduce the WEEKDAY function to be used in the criteria.

    SUM time by WEEKDAY

The formula in K2:L2 are,

=SUMPRODUCT(($D$2:$D$99=$J2)*(WEEKDAY($A$2:$A$99, 2)<6), $H$2:$H$99)
=SUMPRODUCT(($D$2:$D$99=$J2)*(WEEKDAY($A$2:$A$99, 2)>=6), $H$2:$H$99)

Fill down as necessary.

Upvotes: 3

Related Questions