Sumif with children AND distinct

Melisa Dannhauser

Trying to sum the children of ColumnB but only for those rows where there is a DISTINCT value in the children of ColumnA. It seems like it would be simple but I'm blanking. Any help appreciated!

I'm thinking something like

=SUMIF(CHILDREN([ColumnA]@row), DISTINCT(), CHILDREN[ColumnB]))

but that obviously doesn't work because the distinct needs a refrence. I just don't know how to formulate it correctly...

Genevieve P.

Hi @Melisa Dannhauser

What are you referring to when you want a "distinct" value in Column A? Is there a specific value that you're searching for, or a specific number of values you need to check for?

If you're looking for something specific, you can search for it directly with quotes:

=SUMIF(CHILDREN([ColumnA]@row), "Value A", CHILDREN[ColumnB]@row))

If Column A is a multi-select column, you could use HAS instead of Distinct to make sure the cell has that value:

=SUMIF(CHILDREN([ColumnA]@row), HAS(@cell, "Value A"), CHILDREN[ColumnB]@row))

If this hasn't helped, it would be useful to see screen captures of your sheet, but please block out sensitive data.

Cheers,

Genevieve

Melisa Dannhauser

@Genevieve P. No, I'm not looking for a specific value in column A. I'm saying I want to sum the children in column B but ONLY for distinct values in column A. Meaning column A will have multiple duplicate values but I want Smartsheet to only consider the first occurrence of each and then sum the corresponding value in column B. Just like sum(distinct()) but in this case I'm summing items from a different column and that's where my issue lies.

Here's more detail. Column A has Property IDs. Column B has square footage for each property. Column A might have a certain property listed several times. I want to calculate the total square footage for the properties without counting it twice if the property appears twice. My top row is a "summary" row and all the properties are underneath as children so I was hoping I can use the children function. But any formula would work. Preferably without helper columns but will add one if I need to...

Genevieve P.

Hi @Melisa Dannhauser

Thank you for clarifying! I understand, now.

No, I don't believe there is a way to use DISTINCT in this way in a SUMIF formula. The DISTINCT would need to go with the CHILDREN as the range, but it's also the criteria, you're right.

The way I would do this is to have a checkbox column indicate the first instance of a Child so that you could use this helper column instead of Distinct. There's likely a more succinct way of putting this together, but personally I would set up three columns:

• an Auto-Number column (this could also be a System Date column)
• a "Parent" column that duplicates the Parent name into the child rows
• the Checkbox Distinct column with the formula

The "Parent" column has this formula:

=IF(COUNT(ANCESTORS([Project Name]@row)) = 0, "Top Row", PARENT([Project Name]@row))

You can change "Top Row" to be anything else, we just want to keep it from being a blank cell.

The "DISTINCT" column has this formula:

=IF([Row ID]@row = MIN(COLLECT([Row ID]:[Row ID], [Project Name]:[Project Name], [Project Name]@row, Parent:Parent, PARENT([Project Name]@row))), 1)

Then the "SUM" column has this formula:

=SUMIFS([Column to Sum]:[Column to Sum], DISTINCT:DISTINCT, 1, Parent:Parent, [Project Name]@row)

Since we have the Parent formula bringing in the Parent name to each child row, we can use this as a filter in the Sum formula so we can apply the formula for the entire column. If you don't like seeing 0's, we can format this to be blank in the formula as well:

=IF(SUMIFS([Column to Sum]:[Column to Sum], DISTINCT:DISTINCT, 1, Parent:Parent, [Project Name]@row) = 0, "", SUMIFS([Column to Sum]:[Column to Sum], DISTINCT:DISTINCT, 1, Parent:Parent, [Project Name]@row))

Cheers,

Genevieve