I want to be able to take the data presented and fill in another table where the columns are weekday (mon, tues, wed, thurs), weekends (fr, sat , sund) and the rows are the week numbers. The data i need are the sums. For example, how can i extract the sum of all the weekdays/weekends for week 1

CodePudding user response：

You can do it with either SUMPRODUCT or SUMIFS. Each has its advantages. SUMPRODUCT supports a multi-column range, so the formula is simpler, but it's also not as standard, so it's a little harder to read. I have included the data and formulas in a Google Sheet, but I did this on Excel first and just transferred them here for ease of demonstration. Let me know if this helps.

https://docs.google.com/spreadsheets/d/1BAnfIb6MtltkPERnuDN3w6BnP55mlsJOwv1xc5GaWHE/edit#gid=0

Here I have some sample random data in a table, along with some weeknumbers and daynumbers. Your numbers did not look like they were standard, so I didn't try to duplicate them. I just used Excel's standard functions. This will only make a difference in the condition for determining weekends vs weekdays, where I have used 1 and 7. You will just need to replace here with whatever your numbers are.

The SUMPRODUCT versions is relatively straightforward. We use this:

=SUMPRODUCT(($C$3:$C$31=$R3) * (($D$3:$D$31=1) ($D$3:$D$31=7)) * ($E$3:$I$31))

to get the weekends sums, where the first condition is the week number, the second is the day number, and the last is the data range. We invert the day number condition to get the weekday sums:

=SUMPRODUCT(($C$3:$C$31=$R3) * (NOT(($D$3:$D$31=1) ($D$3:$D$31=7))) * ($E$3:$I$31))

The advantage of the SUMPRODUCT is that it supports a multi-column data range, the condition is easily invertible, and if you are familiar with the construction it is relatively simple to follow. A disadvantage is that it may confuse some, and it also doesn't support full columns, meaning you will have to resize the column references if the data changes.

SUMIFS will support full column references, meaning you will only have to write it once, and more people are familiar with SUMIFS, but it also doesn't support multi-column sum ranges, so you have to write five separate sumifs, one for each of the columns in the data set and then add them. Here the IF conditions are simple. One to match the week number and two to make sure the day number doesn't match a weekend.

The weekend variation on the SUMIFS formula is not going to work in Google Sheets, because Sheets handles the arrays slightly differently. It should work in Excel. What we are doing in that is passing SUMIFS an array constant containing the weekend numbers {1,7}, and Excel will generate two columns of results, one for each element of the array constant. To prevent a spill we pass that array to SUM, to get a final total. This gives us the ability to use an OR style logic in SUMIFS.