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'.

[TABLE="width: 580"]
<tbody>[TR]
[TD][/TD]
[TD]Source 1[/TD]
[TD]Source 2[/TD]
[TD]Source 3[/TD]
[TD]Source 4[/TD]
[TD]Source 5[/TD]
[/TR]
[TR]
[TD]Refresher training & workshops[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Personnel[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Communications[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Maintenance[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Supplies, utilities & other recurrent cost[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Buildings[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Equipment[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Vehicles[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Consultants[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]TOTAL[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]


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