Metrics Sheet Doubling values

Andrew Taglauer

Hello,

I am trying to solve have a cross-reference sheet problem that is doubling my values in my metric sheet. I am using =sum(children()) in my source sheet to sum the values under each parent/child row. When I reference the destination sheet for metrics it doubles the value because I think I am referencing to sum all the PO#s that are same value including the PO# that I have referenced for the parent row. Here is the statement I am using to sum all the values from children's rows.

=SUMIF(CHILDREN({All PM PO Tracker Range}, [PO#]@row, {All PM PO- Total Awarded Amount}))

Genevieve P.

Hi @Andrew Taglauer

The CHILDREN function will only work in the same sheet as the hierarchy, it can't be used in cross-sheet formulas.

What I would personally do here is have a helper column in the main source sheet that adds the "Level" to each row.

=COUNT(ANCESTORS([Primary Column]@row))

That way you can reference this helper "Level" column in your cross sheet formula to filter out any levels you don't want... for example:

=SUMIFS({All PM PO- Total Awarded Amount}, {All PM PO Tracker Range}, [PO#]@row, {Level Column}, >1)

Cheers,

Genevieve