• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

V

Vikram Dhemare

New Member
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.
 
Last edited:
vletm said:
... and Your shorter formula has $C$3:$N$500 ... which works.
... reason?
Click to expand...
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:
1733212830128.png
And i want a result in summary sheet:
1733212996469.png
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
 
Yes, my post has been ignored, so I'll repeat my request.

Can you share a desensitised workbook?

Workbook, NOT screenshot. Thanks.
 
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!
 

Attachments

  • Sumproduct(sumifs(Indirect) function.xlsx
    50.5 KB · Views: 4

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"))
 
vletm said:

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"))
Click to expand...
Thank you for your response. However, I did not fully understand. Are you saying that the month condition will not work in this case?
 
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:

Code: 
=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.
 

Attachments

  • Vikram Dhemare Sumproduct(sumifs(Indirect) function(1) SPILL 365 AliGW.xlsx
    49.8 KB · Views: 0
Further, here's a scalable version:

Code: 
=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.
 

Attachments

  • Vikram Dhemare Sumproduct(sumifs(Indirect) function(1) SPILL 365 AliGW.xlsx
    51.1 KB · Views: 1

Vikram Dhemare​

Your Are you saying that the month condition will not work in this case?
As I tried to explain: You try to use something like 1/1/2025 with month(1/1/2025) with Your last condition.
Your layout do not need that part at all.
There are same date ( eg 1/1/2025 which You've formatted to show month & year) columns in all those sheets.
 
Thank you, both! seems to be bit complicated for me to work this. The formula requires 3D sums, matching vertical and horizontal conditions. The columns are formatted to month and year and each column has different month all together, which is one of the condition in summary sheet.
It's my bad as i did not work at all.
Thank you once agains!
 

Vikram Dhemare​

Do You need results or what?
As those columns are those ... You can forget 'need use horizontal condition'!
Those cells which has 'month-year' could be with any format ( test to format to hh:mm - it'll work still in same way )
- it means, what is in cell eg You see Jan-25 but it value is 01/01/2025.
Eg all 01/01/2025 (or Jan-25) are in C-column ... 01/04/2025 (or Apr-25) are in F-column.
The solution (eg formula) needs to find - in which row, those 'Rent' and so on are.
 
You must log in or register to reply here.
Back
Top