CODEWITHSUNDEEP
excelvbaexcel-formulasumifssumproduct
Rhonwyn
Rhonwyn

Reputation: 1

How do I do either a SumIf or SumProduct function with VBA coding to Sum accross multiple sheets

My current formula in the cell is:

=IFERROR(SUMPRODUCT(SUMIF(INDIRECT("'"&Regions&"'!$C:$C"),$B4,INDIRECT("'"&Regions&"'!AI:AI"))),"")

Annoyingly the AI:AI will not change the reference cell when I copy across (only down)

I would like to take the formula out of the cell and have it calculate through a module instead. Any ideas would be fantastic thanks

Upvotes: 0

Views: 165

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

In this formula 

=IFERROR(SUMPRODUCT(SUMIF(INDIRECT("'"&Regions&"'!$C:$C"),$B4,INDIRECT("'"&Regions&"'!AI:AI"))),"")

INDIRECT is using a string/text that looks like an address but since it is only a string (you can see that it is in quotes " ") it doesn't change when it get's copied over to another cell. Actually that is the purpose of INDIRECT (that it does not change).

Example:

  1. =SUM(A:A)
    here A:A is a real address that will change if you copy it to another cell.
  2. =SUM(INDIRECT("A:A"))
    here "A:A" is a string and this will not change if you copy it to another cell!

So you can workaround that by replacing

INDIRECT("'"&Regions&"'!$C:$C")

with 

INDIRECT("'Regions'!" & ADDRESS(1,COLUMN(C:C)) & ":" & ADDRESS(ROWS(C:C),COLUMN(C:C)))

How does that work?

  • ADDRESS(1,COLUMN(C:C)) returns $C$1
  • ADDRESS(ROWS(C:C),COLUMN(C:C)) returns $C$1048576

Together this will be $C$1:$C$1048576 which is the same as C:C. And because here C:C in your formula is a real address and not a string it will change if you copy it over.

INDIRECT is volatile!

Note that INDIRECT is a volatile function. That means it recalculates everytime any value of any cell changes.

Example:

  1. =SUM(A:A)
    this will only re-calulate if a value in A:A changes if a value in B:B changes it doesn't affect recalculation of this formula.
  2. =SUM(INDIRECT("A:A"))
    this will re-calculate on any value change even if a value in B:B changes it re-calculates.

Therefore the heavy use of volatile functions should be avoided because it slows down your worksheet a lot.

Upvotes: 1

Related Questions