Sumproduct with sumifs across sheets using indirect unction, gives #value error

[Vikram Dhemare]

Hi everyone,

I’m working on summarizing data from multiple sheets using the INDIRECT function with three criteria: GL code, currency, and month. However, I keep running into a #VALUE! error when attempting the formula below:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheets&"'!$C$4:$N$500"),INDIRECT("'"&Sheets&"'!$A$4:$A$500"),$B8,INDIRECT("'"&Sheets&"'!$B$4:$B$500"),"USD",INDIRECT("'"&Sheets&"'!$C$3:$N$3"),MONTH(D$2)))

I’ve spent two days troubleshooting this issue but haven’t been able to resolve it. Any advice would be greatly appreciated!

Interestingly, when I simplify the formula and use only the SUMIF function, I get the results: but not when using SUMIFS function

=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!$A$4:$A$500"),$B5,INDIRECT("'"&Sheets&"'!$C$4:$N$500")))

Does anyone know why the first approach isn’t working or how I can fix it? Thanks in advance for your help!

Sheets is a named range which has sheets names.

 

[vletm]

Vikram Dhemare​

Is there any reason why this range $C$3:$N$3 is different than other ranges ( from 4 to 500 )?

 

[AliGW]

Can you share a desensitised workbook?

 

[Vikram Dhemare]

Vikram Dhemare​

Is there any reason why this range $C$3:$N$3 is different than other ranges ( from 4 to 500 )?

those are columns heading for months, say Jan to Dec in each sheet with same range group. And i want the summary selecting the month crieteria

 

[vletm]

... and Your shorter formula has $C$3:$N$500 ... which works.
... reason?

 

[Vikram Dhemare]

... and Your shorter formula has $C$3:$N$500 ... which works.
... reason?

No it does not...
When i use only sumif (which ultimately refers one condition), it works...but with multiple conditions when i try to use sumifs then it don't.
this is what i have in each sheet:

And i want a result in summary sheet:

I used the formula: =SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheets&"'!$C$4:$N$500"),INDIRECT("'"&Sheets&"'!$a$4:$a$500"),$B4,INDIRECT("'"&Sheets&"'!$b$4:$b$500"),"USD",INDIRECT("'"&Sheets&"'!$C$3:$N$3"),MONTH(C$2)))
where sheets refer to named range

 

[vletm]

Did You skip #3 reply?

 

[AliGW]

Yes, my post has been ignored, so I'll repeat my request.

Can you share a desensitised workbook?

Workbook, NOT screenshot. Thanks.

 

[Vikram Dhemare]

Hi Ali, Apologies for missing your earlier request. The workbook is attached as requested. I’m unsure where I am going wrong with the formula. Any guidance or suggestions you can provide would be greatly helpful!

 

[vletm]

Vikram Dhemare​

Your INDIRECT("'"&Sheets&"'!$C$3:$N$3"),MONTH(D$2)) never works (as be true)! ... You try to test date to month
After that You could use something like below:
=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheets&"'!C$4:c$500"),INDIRECT("'"&Sheets&"'!$a$4:$a$500"),$B3,INDIRECT("'"&Sheets&"'!$b$4:$b$500"),"USD"))

 

[Vikram Dhemare]

Vikram Dhemare​

Your INDIRECT("'"&Sheets&"'!$C$3:$N$3"),MONTH(D$2)) never works (as be true)! ... You try to test date to month
After that You could use something like below:
=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheets&"'!C$4:c$500"),INDIRECT("'"&Sheets&"'!$a$4:$a$500"),$B3,INDIRECT("'"&Sheets&"'!$b$4:$b$500"),"USD"))

Thank you for your response. However, I did not fully understand. Are you saying that the month condition will not work in this case?

 

[AliGW]

Are you using 365?

If so, try removing ALL expected results from the months columns of the grid and paste this into C3 followed by ENTER:

=DROP(REDUCE("",B3:B10,LAMBDA(x,y,VSTACK(x,BYCOL(FILTER(VSTACK(USA:UK!C4:N500),(VSTACK(USA:UK!A4:A500)=y)*(VSTACK(USA:UK!B4:B500)="USD")),LAMBDA(c,SUM(c)))))),1)

If you do NOT have 365, this will not work for you.

 

[AliGW]

Further, here's a scalable version:

=DROP(REDUCE("",B3:B10,LAMBDA(x,y,VSTACK(x,BYCOL(FILTER(VSTACK(Start:Finish!C4:N500),(VSTACK(Start:Finish!A4:A500)=y)*(VSTACK(Start:Finish!B4:B500)="USD")),LAMBDA(c,SUM(c)))))),1)

I added two empty sheets: Start and Finish. Make sure that ALL source sheets that need to be referenced are placed between these two tabs.