Sumproduct for multi worksheet

[vcheung]

Hi,

I created a formula by sumproduct in a single worksheet =SUMPRODUCT(('PL2001'!$A$8:$A$125=$A16)*('PL2001'!C$5=C$5)*('PL2001'!$C$8:$C$125)) and there are 12 worksheets to have such formula and sum together, so I set the formula as =SUMPRODUCT(('PL2001'!$A$8:$A$125=$A16)*('PL2001'!C$5=C$5)*('PL2001'!$C$8:$C$125))+......SUMPRODUCT(('PL2012'!$A$8:$A$125=$A16)*('PL2012'!C$5=C$5)*('PL2012'!$C$8:$C$125)) but it returns "#VALUE!", may I know what is the reason to have such error value?

And please help to provide the solution how to set this formula across 12 worksheets?

Thanks!

 

[jasonb75]

Based on what you have shown us the formula should work fine with multiple sheets using your method. The only possible reason for a #VALUE! error would be text in the range C8:C125 (which would include any formulas that show a blank cell).

If that is the case then changing the last asterisk in each sumproduct formula to a comma will rectify it.

=SUMPRODUCT(('PL2001'!$A$8:$A$125=$A16)*('PL2001'!C$5=C$5),('PL2001'!$C$8:$C$125))

If you have errors in any of the cells referred to by the formula then it would be better to correct them at the source rather than working around them in the formula here.

 

[maabadi]

For Sum at all Worksheets, Use this Macro:
First Change Set Ws1 = Sheets("Sheet1") to your formula sheet (if it's Name is sheet3 then Set Ws1 = Sheets("Sheet3") )

Sub SumifsMultyWS()
Dim Ws As Worksheet, S As Double, WsName As String, Ws1 As Worksheet, i As Long, T As Variant
Dim Cr1 As Range, Cr2 As Range, CrR1 As Range, CrR2 As Variant, SumRange As Range
Set Ws1 = Sheets("Sheet1")
Set Cr1 = Ws1.Range("A16")
Set Cr2 = Ws1.Range("C5")
S = 0
For Each Ws In Worksheets
For i = 2001 To 2012
If Ws.Name = "PL" & i Then
Set SumRange = Ws.Range("C8:C125")
Set CrR1 = Ws.Range("A8:A125")
Ws.Range("XF8:XF125") = Ws.Range("C5")
Set CrR2 = Ws.Range("XF8:XF125")
S = Application.WorksheetFunction.SumIfs(SumRange, CrR1, Cr1, CrR2, Cr2)
T = S + T
Ws.Range("XF8:XF125") = ""
GoTo Resum
End If
Next i
Resum:
Next Ws
End Sub

 

[vcheung]

Based on what you have shown us the formula should work fine with multiple sheets using your method. The only possible reason for a #VALUE! error would be text in the range C8:C125 (which would include any formulas that show a blank cell).

If that is the case then changing the last asterisk in each sumproduct formula to a comma will rectify it.

=SUMPRODUCT(('PL2001'!$A$8:$A$125=$A16)*('PL2001'!C$5=C$5),('PL2001'!$C$8:$C$125))

If you have errors in any of the cells referred to by the formula then it would be better to correct them at the source rather than working around them in the formula here.

Hi, Jason! Thanks for your advice and it works after changing the asterisk to comma. I also want to know is it possible to list the worksheet name in a table and re-form this formula by reading the worksheet name to the table with the other functions by "SUMPRODUCT"? I do think it can make shorter the formula instead of adding the sumproduct on top. Look forward to your comment with thanks.

 

[vcheung]

For Sum at all Worksheets, Use this Macro:
First Change Set Ws1 = Sheets("Sheet1") to your formula sheet (if it's Name is sheet3 then Set Ws1 = Sheets("Sheet3") )

Sub SumifsMultyWS()
Dim Ws As Worksheet, S As Double, WsName As String, Ws1 As Worksheet, i As Long, T As Variant
Dim Cr1 As Range, Cr2 As Range, CrR1 As Range, CrR2 As Variant, SumRange As Range
Set Ws1 = Sheets("Sheet1")
Set Cr1 = Ws1.Range("A16")
Set Cr2 = Ws1.Range("C5")
S = 0
For Each Ws In Worksheets
For i = 2001 To 2012
If Ws.Name = "PL" & i Then
Set SumRange = Ws.Range("C8:C125")
Set CrR1 = Ws.Range("A8:A125")
Ws.Range("XF8:XF125") = Ws.Range("C5")
Set CrR2 = Ws.Range("XF8:XF125")
S = Application.WorksheetFunction.SumIfs(SumRange, CrR1, Cr1, CrR2, Cr2)
T = S + T
Ws.Range("XF8:XF125") = ""
GoTo Resum
End If
Next i
Resum:
Next Ws
End Sub

Hi, Maabadi! Thanks for your idea to solve my question by VBA and I will try it.

 

[maabadi]

You're Welcome.

 

[jasonb75]

I do think it can make shorter the formula instead of adding the sumproduct on top.

There is a way to possibly shorten it although it would be far less efficient than the full formula due to the functions that are used (INDIRECT is required which is a volatile function), in reality it would only really be of benefit if you want to vary which sheets are included by adding or removing names that are in the list.

That said, the tried and tested method is when all of the sumproduct ranges are equal in size, noting that your second criteria is only a single cell it might not work for you. I'm a bit short of time now but I'll set up a quick test file when I get home this evening and see if it will work.

Also, remember that using the vba method, the results will not update automatically unless you make it an event which again could be less efficient than the long formula. You could try it as a udf instead but that would need some changes and may also mean that the formula needs to be volatile in order to recalculate properly.

 

[jasonb75]

For your version of excel, the shortest formula that will work is

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&$A$2:$A$13&"'!C5"),C$5)*SUMIFS(INDIRECT("'"&$A$2:$A$13&"'C8:C125"),INDIRECT("'"&$A$2:$A$13&"'!A8:A125"),$A16))

Where $A$2:$A$13 contains a list of sheet names. Note that empty cells or names of sheets that do not exist will cause #REF! errors.
The INDIRECT ranges are always absolute (meaning that they will not change if you drag / fill the formula) adding or removing the $ symbols will have no effect on them. There are ways to make them relative but this further increases the complication of the formula.

It may be possible to simplify it slightly by using the LET function in office 365, I have not tested this as you will not have it in excel 2016.

 

[vcheung]

For your version of excel, the shortest formula that will work is

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&$A$2:$A$13&"'!C5"),C$5)*SUMIFS(INDIRECT("'"&$A$2:$A$13&"'C8:C125"),INDIRECT("'"&$A$2:$A$13&"'!A8:A125"),$A16))

Where $A$2:$A$13 contains a list of sheet names. Note that empty cells or names of sheets that do not exist will cause #REF! errors.
The INDIRECT ranges are always absolute (meaning that they will not change if you drag / fill the formula) adding or removing the $ symbols will have no effect on them. There are ways to make them relative but this further increases the complication of the formula.

It may be possible to simplify it slightly by using the LET function in office 365, I have not tested this as you will not have it in excel 2016.

Hi, Jason! Thanks for your valuable advice and it is very useful and powerful.

P.S. The "!" is missing in front of C8:C125 in your example =SUMPRODUCT(COUNTIFS(INDIRECT("'"&$A$2:$A$13&"'!C5"),C$5)*SUMIFS(INDIRECT("'"&$A$2:$A$13&"'!C8:C125"),INDIRECT("'"&$A$2:$A$13&"'!A8:A125"),$A16))