• 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: 5

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: 2

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.
 
Thank you, both! seems to be bit complicated for me to work this.
Click to expand...

Do you want an explanation so that you can learn, or are you just going to dismiss this solution because you don't want to be bothered with learning new tricks? If the latter, then I'll step aside and leave it to someone else. The solution matches the rows, but there is no need to match columns as they are all the same on all source sheets. Unless you provide sample data that shows otherwise, there's no point in complicating matters further.
 
Hi Ali,
Thank you for providing the solution. While your solution is advanced and works well for this specific worksheet, I find it a bit complex to implement for broader use cases. A simpler and more familiar method would be easier for both myself and other users to adopt and apply consistently in workbooks over time.
While researching online, I came across a solution that involves using SUMPRODUCT combined with SUMIFS across sheets via the INDIRECT function. I believe this approach might be more user-friendly and adaptable, even in my absence.
Your solution is indeed sophisticated, and I appreciate the effort you put into it. However, I’m curious why the SUMPRODUCT with SUMIFS method didn’t work in this case. My setup involves two vertical columns to match against and a horizontal column for months, with the goal of summarizing results across sheets based on these criteria.
To better understand this, I’ll create a new worksheet with different ranges and test it further.
Although I’m eager to learn advanced techniques, I’m working against a deadline and am considering simpler alternatives for now—perhaps something like ='USA'!C79+'France'!C56+'Spain'!C45.
Thank you again for your help!
Vikram
 
The problem is, though, that the solution you found doesn't work fully - which is why you are asking here. I did spend some time trying to make it work for you, but could not, which is why I offered the more complex and newer approach, which I am happy to explain to you if you wish. Take this offer or leave it - it's up to you.

The only bits of this formula that you or any colleague would ever need to change are in red below:

=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)

Three ranges only. But as I said, it's up to you. I don't actually think it's any harder for anyone to update than your own attempt with SUMIFS and SUMPRODUCT, and I would always avoid using INDIRECT wherever possible. But as I said, it's up to you.
 
Hi Ali,
Thank you for taking the time to explore the issue and for offering a more advanced solution. I truly appreciate the effort you’ve put into trying to make the formula work for my specific case.
I understand the rationale behind your approach and your recommendation to avoid using INDIRECT. The formula you’ve shared, with clear indications of the editable parts, seems like a robust alternative that could address the limitations I faced.
Understanding the logic behind it would help me (and my colleagues) feel more confident in implementing and updating it as needed.
Thanks again for your guidance—I look forward to learning more about your suggested solution!
 
In the attached I have broken the formula down to show what each bit of it does. Please read the information carefully and ask if anything isn't clear.
 

Attachments

  • Vikram Dhemare Sumproduct(sumifs(Indirect) function(1) SPILL 365 AliGW.xlsx
    54.3 KB · Views: 2
Thank you, Ali, for breaking down the formula and providing the detailed explanation. I’ll go through the information carefully and let you know if I have any questions or need further clarification.

Appreciate your effort in making this clearer!
 
You must log in or register to reply here.
Back
Top