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

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

Paul Newcome

Try

=VALUE(PARENT())

instead.

Paul Newcome

Happy to help. 👍️

Paul Newcome

What is the formula you are using to populate the numbers in the Wave column?

melimob

=parent

melimob

The issue is that it's just a number. So, in the column it is stored as text but when you have it in sheet summary it only recognises as a number.

Hence in a metrics sheet I can accommodate for both instances by adding two versions of countifs, but in a sheet summary with a drop down I am stuck?

Metrics: =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}, AUSTRALIA21, {DS2024_Master Plan_Wave}, 1)

Paul Newcome

Try

=VALUE(PARENT())

instead.

melimob

AMAZING!!! thank you Paul, works like a charm ;)

Paul Newcome

Happy to help. 👍️