Sum from multiple columns per week

Pam Dunn

I am creating a high level sheet with only weekly totals. I want to sum by week the 3 columns highlighted in yellow that are not next to each other in the same sheet (example only). The Week column is a formula =IFERROR(WEEKNUMBER(Date@row), "") .

This is the formula I have tried I get error: Incorrect Argument Set.

=SUMIFS({Week}, 27, {Copywritten}, {Copyedit}, {Copyproof})

When I try it with only one column say the copy written one the formula works, but when I add the other two columns it doesn't work.

I then tried this still same error.

=SUM(SUMIFS({Week}, 27, {Copywritten}, SUMIFS({Weekof}, 27, {Copyedit}, SUMIFS({Weekof}, 27, {Copyproof}))))

Rich Stowell

Try this formula

=SUMIF(Week, 27, [copy written]: [copy written]) + SUMIF (Week, 27, [copy edited]: [copy edited]) + SUMIF (Week, 27, [copy proofed]: [copy proofed])

NOTE: the names of the columns must be exact as they are on the sheet.

Mark Safran

Hi Pam,

I think the Collect( ) function nested in a SUM( ) will be your best bet here. This will return the range of values for each of your yellow columns that meet the desired condition (Week#):

=SUM(COLLECT([Copy Written]:[Copy Written], Week:Week, 27), COLLECT([Copy Editted]:[Copy Editted], Week:Week, 27), COLLECT([Copy Proofed]:[Copy Proofed], Week:Week, 27))

-MS

Pam Dunn

@Mark Safran ,

Thanks Mark, but this did not work. When I choose the field from my document (reference another sheet) it places these type of brackets { } around the field, but you are showing [ ] brackets how does this impact the formula and functionality?

Also why are there no brackets around Week. I would do the same thing select my column (reference another sheet) and select the Week which places the { } brackets around them.

I even made sure the column names were correct, added " " around the week number, tried with without the " " and so on. Nothing is working.

I am getting the error unparseable

Mark Safran

Hi Pam,

The braces { } are used when you are referencing a Range from another sheet, and the Sheet Reference Name (the text that goes within the braces) is whatever you define that to be. The brackets [ ] are used when you are referencing a Range/column from the same sheet, and the Column name is more than 1 word long. In your example, you had your excel headers as "Copy Written", "Copy Editted", etc., so all of them utilize the brackets except for Week (since that's a single word). My original formula above assumed you were doing all this math in the same sheet - apologies.

If your Sheet Reference Names are {Week}, {Copywritten}, {Copyedit}, and {Copyproof}, you can just replace those as the ranges in the formula I gave above. See here:

=SUM(COLLECT({Copywritten}, {Week}, 27), COLLECT({Copyedit}, {Week}, 27), COLLECT({Copyproof}, {Week}, 27))

Your original post also references "{Weekof}" though as a Sheet Reference Name, so depending on which one of those is the correct range/reference, you made need to use that instead. If you'd like to share your two sheets with me I can also take a look directly to get it right.

-MS

Rich Stowell

{} denotes a range referenced to another sheet. You will one entry to for the column reference

'[x x] is used to indicate a column in the current sheet that has a space in it's name. You need the [x x]:[x x] to denote the column from the current sheet

There are no brackets around Week as there is no space in the column name.

Rich Stowell

Try this formula

=SUMIF(Week, 27, [copy written]: [copy written]) + SUMIF (Week, 27, [copy edited]: [copy edited]) + SUMIF (Week, 27, [copy proofed]: [copy proofed])

NOTE: the names of the columns must be exact as they are on the sheet.

Chris Martin

@Mark Safran your collect formula worked perfectly for me. cheers