Sum from multiple columns per week

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}))))


Best Answer

  • Rich Stowell
    Rich Stowell ✭✭✭✭✭
    Answer ✓

    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.

Answers

  • Mark Safran
    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
    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
    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
    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
    Rich Stowell ✭✭✭✭✭
    Answer ✓

    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
    Chris Martin ✭✭✭✭

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!