SUMIFS formula that includes a checkbox condition
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

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!

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

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!

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

That was quick! Thank you both for your help!


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
Categories
Check out the Formula Handbook template!