SUMIFS formula that includes a checkbox condition

Kristina Todoric

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

Jeff Reisman

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

mattstoc

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

Jeff Reisman

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

mattstoc

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

That was quick! Thank you both for your help!

Kristina Todoric

JDI

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