Returning a value based on multiple criteria

I am trying to autofill a summary table using formulas. I am able to autofill the monthly counts using date criteria, but I can't get the total numbers to autofill.

I tried using the below formulas to pull from the two places where I have the counts for total members and total new members of each member type, but I keep getting a #BLOCKED error message that impacts my other formulas.

=INDEX(COLLECT([Associate Healthcare]:[Associate Healthcare], Year:Year, YEAR(TODAY()), Ancestors:Ancestors, 0, New:New, false), 1)

=INDEX(COLLECT([Associate Healthcare]:[Associate Healthcare], Year:Year, YEAR(TODAY()), Ancestors:Ancestors, 0, New:New, true), 1)

Is it not pulling because I already have formulas in the cells the data would be coming from? Or is there another formula I should be using to accomplish this?


Tags:

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!