Alternative to INDIRECT to SUMIF in multiple worksheets

ongcaps

New Member
Joined
Sep 7, 2016
Messages
12
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 1Source 2Source 3Source 4Source 5
Refresher training & workshops00000
Personnel00000
Communications00000
Maintenance00000
Supplies, utilities & other recurrent cost00000
Buildings00000
Equipment00000
Vehicles00000
Consultants00000
TOTAL00000

<tbody>
</tbody>


Is there any alternative to INDIRECT in this formula?

Thank you very much in advance.

Cheers,
Daniel
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,007
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
Joined
Sep 7, 2016
Messages
12
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
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
Joined
Dec 23, 2017
Messages
1,007
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
            ' 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
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top