SUMIFS formula that includes a checkbox condition

Options

Hi everyone,

I'd like to sum all cells in a column that are (A) in a parent row (B) have a certain name in the Producer column. I've inserted a checkbox column (called "Parent") with a formula to check the box if the row is a parent row (which works) but I can't get the SUMIFS formula to work.

I've pasted the two I've tried below, it would be great if someone could help me with this and let me know where I'm going wrong.

(INVALID OPERATION)

=SUMIFS(Parent$3:Parent$74, =1, [Producer(s)]3:[Producer(s)]74, "Kristina", [w/c 17 Jan]3:[w/c 17 Jan]74)

(INCORRECT ARGUMENT SET)

=SUMIFS(Parent$3:Parent$74, "1", [Producer(s)]3:[Producer(s)]74, "Kristina", [w/c 17 Jan]3:[w/c 17 Jan]74)


Thanks,

Kristina

Best Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    It looks to me like you've just gotten your elements out of order. In SUMIFS, the column you want to add up goes first, followed by criteria range 1, criteria 1, criteria range 2, criteria 2...

    Try this:

    =SUMIFS([w/c 17 Jan]3:[w/c 17 Jan]74, Parent$3:Parent$74, =1, [Producer(s)]3:[Producer(s)]74, "Kristina")

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • mattstoc
    mattstoc ✭✭
    Answer ✓
    Options

    Here is the formula that I used (I recreated a similar sheet)

    =SUMIFS([WC Value]1:[WC Value]18, Parent1:Parent18, 1, [Producer(s)]1:[Producer(s)]18, "Kristina")

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    It looks to me like you've just gotten your elements out of order. In SUMIFS, the column you want to add up goes first, followed by criteria range 1, criteria 1, criteria range 2, criteria 2...

    Try this:

    =SUMIFS([w/c 17 Jan]3:[w/c 17 Jan]74, Parent$3:Parent$74, =1, [Producer(s)]3:[Producer(s)]74, "Kristina")

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • mattstoc
    mattstoc ✭✭
    Answer ✓
    Options

    Here is the formula that I used (I recreated a similar sheet)

    =SUMIFS([WC Value]1:[WC Value]18, Parent1:Parent18, 1, [Producer(s)]1:[Producer(s)]18, "Kristina")

  • Kristina Todoric
    Kristina Todoric ✭✭✭✭
    Options

    That was quick! Thank you both for your help!

  • Kristina Todoric
    Kristina Todoric ✭✭✭✭
    edited 11/01/22
    Options
  • JDI
    JDI ✭✭
    Options

    I'm working on a similar problem. After indenting/outdenting, my rollup isn't keeping track of if a task is/was a parent and calculating the rollup of the child tasks correctly.  

    I've created a new column that I can hopefully apply after any project manipulations. I want it to simply look at the row to determine if this is a parent task (checkbox is checked or not), and then add child tasks from the "Budget" column.

    Scenario:

    I had a project with Task 1; Task 2 (subtask 2a, subtask 2b). Therefore, Task 1 is not a parent, and task 2 is. However, if I roll Task 2 under task 1, I currently don't get a rollup calculation. Same thing happens if I just add subtask 1a, 1b, 1c.

    Here's what I have, but it's #unparseable:

    =if(isParent),true,SUM(CHILDREN(Budget),"")))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!