SUMIFS HELP, BLOCKED, CIRCULAR REFERENCE

leapm

I am trying to get "Points" totals in the Parent rows for rows that are "active" within April and also have "Enterprise" as the "LOB". I want to do this for multiple parent rows with different LOB names.

Any help is appreciated.

Jeff Reisman

@leapm Smartsheet formulas consider criteria from left to right. Since your formula is in one of the columns you use for criteria, you want to exclude that row from consideration first.

=SUMIFS(Points:Points, LOB:LOB, "Enterprise", April:April, "Active")

This way, before it considers the April column, the row the formula is on is already out of the picture, eliminating the circular reference.

leapm

Thank you for getting back to me so quickly. It's still happening unfortunately. Not for the first row, but for the subsequent rows where I'm also trying to sum specific values. Any other tips? Appreciate it!

help2.png

Jeff Reisman

https://community.smartsheet.com/discussion/comment/361648#Comment_361648

OK, so you'll have to specifically reference only child rows of the row you're on.

=SUMIF(CHILDREN(), "Active", CHILDREN(Points@row))

This will only consider child rows of "Enterprise" with Active in the April column, and will only count points in the Child rows of the Points cell on that row.

leapm

Woo hoo! Thank you so much. You have no idea how much time I spent trying to make that work. Much respect.