Countifs where sheet range is formula returning a number and criterion is sheet summary dynamic drop

melimob
melimob ✭✭✭

I have a column called '"Wave" which shows a number 1-10. The value is returned on some rows from a =PARENT formula and on some rows it's hardcoded. (I understand it should be either formula or hardcoded but go with me here...)

I have a sheet summary which works perfectly well when the 'Wave' column in my sheet is hardcoded:



But where wave 1 is from a formula:


It returns 0 instead of 3.


In a metrics sheet cell, I could change my formula to achieve the right results:

=COUNTIFS({DS2024_Master Plan_Status}, $[Primary Column]@row, {DS2024_Master Plan_Children}, "0", {DS2024_Master Plan_Country}, AUSTRALIA21, {DS2024_Master Plan_Wave}, ="1")

=COUNTIFS({DS2024_Master Plan_Status}, $[Primary Column]@row, {DS2024_Master Plan_Children}, "0", {DS2024_Master Plan_Country}, GERMANY21, {DS2024_Master Plan_Wave}, 1)

I know also there is something I can do with the value function (but can't get it to work).

So my questions are:

1) is there any ONE formula that would return the right values based on end result of "Wave" irrespective if formula behind or text value? (for my metrics sheet)

2) What would be the formula to make this work considering in my sheet summary, where my criterion in this case is a drop down?

Even though the real answer here is probably to ensure my wave column follows either a formula and lock or hardcoded, I would like to have the best formula to use where it is not hardcoded as this would be needed for my sheet summary.

Many thanks in advance

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!