How to change this Excel formula

Question

i have this formula to get data to one sheet from another sheet. But i need to have a formula that will get data from all the sheets between sheet "start" and sheet "end" i have about 80 sheets.

Thank you.

= COUNTIF(Huvudlager!$B:$B;"Skruvdragare")

Answer 1

There is not a clean and easy way to do this all in one cell.

You can however, take advantage of some nifty excel functions to help you with this, namely GET.WORKBOOK and INDIRECT.

Step 1

Define a Named Range called SheetList (or whatever you want) with the following formula:

=SUBSTITUTE(GET.WORKBOOK(1),"["&GET.WORKBOOK(16)&"]","") creates an array of worksheet names within the workbook. The substitute function gets rid of the workbook name in the array.

Step 2

Highlight 80 rows (since you said you have 80 sheets) of data on the sheet where you wish to count your data. Then type =TRANSPOSE(SheetsList) into the first cell and Ctrl+Shift+Enter. Your sheet list will appear in the 80 rows. Transpose is needed so the list goes down the rows instead of across columns.

Step 3

Write the following formula next to each sheet =COUNTIF(INDIRECT(A1&"!$B:$B"),"Skruvdragare") assumes first sheet name is A1.

Edit: You'd be better served to define the row ranges in the COUNTIF formula, since counting every row in a column is very efficient, especially if not all the rows have data. So, use the max number of rows you know will have data across all the sheets, for example: INDIRECT(A1&"!$B$1:$B$1000")

Step 4

Write a formula to sum the counts of each sheet.