# Alternative to INDIRECT to SUMIF in multiple worksheets

#### ongcaps

##### New Member
Hi Community,

I am using Excel 2010.

I have a problem with the formula below because it makes the workbook VERY slow:

Code:
``=IF(B\$2="",0,SUMPRODUCT(SUMIF(INDIRECT("'"&Tabs&"'!B25:B245"),B\$2,INDIRECT("'"&Tabs&"'!L25:L245"))))``
I am trying to do a SUMIF in 80 worksheets (named range 'Tabs') and the structure of each of the 80 tabs is the same. B\$2 in this case is 'Source 1' in the table below. I have a similar formula in each of the cells with '0'.

 Source 1 Source 2 Source 3 Source 4 Source 5 Refresher training & workshops 0 0 0 0 0 Personnel 0 0 0 0 0 Communications 0 0 0 0 0 Maintenance 0 0 0 0 0 Supplies, utilities & other recurrent cost 0 0 0 0 0 Buildings 0 0 0 0 0 Equipment 0 0 0 0 0 Vehicles 0 0 0 0 0 Consultants 0 0 0 0 0 TOTAL 0 0 0 0 0

<tbody>
</tbody>

Is there any alternative to INDIRECT in this formula?

Thank you very much in advance.

Cheers,
Daniel

#### offthelip

##### Well-known Member
Have you thought abut using VBA and variant arrays to do the whole calcualtion. It should be very fast and will speed up the Excel recalculation enormously

#### ongcaps

##### New Member
Thank you offthelip.

I am not familiar with variant arrays. How can you build them?

I was thinking to put all the information from the 80 sheets into a single table and do the calculations from there. Is this what you are proposing?

Cheers,
Daniel

##### MrExcel MVP
It's definitely a good idea to have all of the data in a single sheet and process the data with the native functions.

#### offthelip

##### Well-known Member
This code will loop through all worksheets in the workbook except "Sheet1" which I have excluded to show you how to avoid any you don't want included
It will then sum the values in column L which mathch the text in column A. It will also do it all in a flash!! I have commented the code to led you through what it is doing
Note I don't use option explicit I prefer to use my time typing comments
Code:
``````Sub tewst()

With Worksheets("Sheet1")
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
' get list to do the mathc on
inarr = Range(Cells(1, 1), Cells(lastrow, 2))
End With
WS_Count = ActiveWorkbook.Worksheets.Count
' loop through all worksheets
For i = 1 To WS_Count
' excleude any worksheets here
If ActiveWorkbook.Worksheets(i).Name <> "Sheet1" Then
' load the data to search into a variatn array

sercharr = Worksheets(i).Range("B25:L245")

' loop through all the varaibles to search for
For j = 1 To lastrow
' loop through the search data
For k = 1 To 220
' look for a match
If inarr(j, 1) <> "" Then
If inarr(j, 1) = sercharr(k, 1) Then
inarr(j, 2) = inarr(j, 2) + sercharr(k, 11)
End If
End If
Next k
Next j
End If
Next i
' write the varaint array out to worksheet
With Worksheets("Sheet1")

Range(Cells(1, 1), Cells(lastrow, 2)) = inarr
End With

End Sub``````

1,082,316
Messages
5,364,483
Members
400,802
Latest member
RichBRich

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...