Alternative to INDIRECT to SUMIF in multiple worksheets

[ongcaps]

Hi Community,

I am using Excel 2010.

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

=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]

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]

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

 

[Aladin Akyurek]

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]

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

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
            ' add into the sum
            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