Dynamic range in formula

[Deutz]

Hi and thanks in advance.

I have a workbook with 50 sheets named by number from 1 to 50

On each named sheet in cell A2 will be an outcome of 'Pass' or 'Fail' that the user will enter after running each sheet process

On a Stats sheet I want to output the total number of sheets that contain 'Pass' in cell A2

However, the number of sheets that are tested are variable.

For instance, the user may be allocated tests/sheets from 1 to 10 or from 25 to 40 or 1 to 50 etc.

So, on the Stats sheet I have a Start sheet name/number cell and an End sheet name/number cell that the defines the range of sheets to be actioned.

I was thinking to use the INDIRECT function to pick up those start and end values and sumproduct them somehow to get the total of 'Pass'

I tried this =SUMPRODUCT(COUNTIF(INDIRECT("'"&F3:G3&"'!A2"),"Pass")) ... where cell F3 is the start sheet name and cell G3 is the end sheet name ... however, it did not give me the whole range of sheets defined.

Have had a few goes without success?

Any help would be appreciated

 

[Tetra201]

Try

=SUMPRODUCT(COUNTIF(INDIRECT("'"&SEQUENCE(G3-F3+1,,F3)&"'!A2"),"Pass"))

 

[Deutz]

Try

=SUMPRODUCT(COUNTIF(INDIRECT("'"&SEQUENCE(G3-F3+1,,F3)&"'!A2"),"Pass"))

Thanks Tetra201, that works exactly as I require