asked on

How many sheets in this range

Hi,

See formula below - which covers a group of excel sheets.

=SUM('Sheet1:Sheet20'!B12)

This add up the value in B12 from 20 different sheets (approx). However, I may add or delete sheets.

QUESTION: I want another formula which COUNTS the number of sheets in this range.

How do I write a formula that simply tells me how many sheets are in the range - as it wont always be 20.

Jorgen

Created a named formula and use an old XLM 4 command =GET.WORKBOOK(4)

Jorgen

To be a little more specific.

By creating a named formula go to Formulas -> Name Manager -> Type a name e.g. NumSheets and in the refers to type in =GET.WORKBOOK(4)

Then you can concatenate you sum formula to include the first sheet up until the number specified.

If you need help with the concatenation - please let me know.

regards

Jørgen

Patrick O'Dea

ASKER

Thanks Jørgen ,

I don't follow you explanation ....

Any chance you could attach a very simple example.

Thanks again,

ASKER CERTIFIED SOLUTION