• 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.

SUMIF(SUMPRODUCT(INDIRECT Formula Malfunction

Leighbrek

New Member
Good evening,

To start off, I'm not an Excel expert, so please break down your response to me like I don't know anything.

What I've set up is a workbook that has multiple tabs that contain the exact same columns as each other. On the first sheet I have a list of values (product names), and beside each I have this formula:
=SUMPRODUCT(SUMIF(INDIRECT("'"&Divisions&"'!I3:I300"),"C9400-LC-24XS",INDIRECT("'"&Divisions&"'!J3:J300")))

I have "Divisions" defined as a name to reference all sheets I'm working with.
This formula works with about 15/20 product names; it returns the sum of values entered on each sheet. The problem is that those other 5 product names ALWAYS return a 0. I conducted a formula evaluation and everything looks as it should up until the SUMPRODUCT portion, where it shows a 0 value across all the sheets, no matter what value is actually in the reference.
This EXACT same formula (with the only difference being the product names) works for everything else.

Can anyone tell me what's going on?

Thanks.
 

Leighbrek

New Member
Hi Rahul,

I had to sanitize my file to remove any business references, but the problem remains the same..I think this file should suffice.


Thanks!
Megan
 

Attachments

vletm

Excel Ninja
Leighbrek
If You could use [@Value] instead something manually written then this would work
cell C4 and down =SUMPRODUCT(SUMIF(INDIRECT("'"&Divisions&"'!G3:G300"),[@Value],INDIRECT("'"&Divisions&"'!H3:H300")))
cell C13 and down =SUMPRODUCT(SUMIF(INDIRECT("'"&Divisions&"'!I3:I300"),[@Value],INDIRECT("'"&Divisions&"'!J3:J300")))
 

Leighbrek

New Member
Leighbrek
If You could use [@Value] instead something manually written then this would work
cell C4 and down =SUMPRODUCT(SUMIF(INDIRECT("'"&Divisions&"'!G3:G300"),[@Value],INDIRECT("'"&Divisions&"'!H3:H300")))
cell C13 and down =SUMPRODUCT(SUMIF(INDIRECT("'"&Divisions&"'!I3:I300"),[@Value],INDIRECT("'"&Divisions&"'!J3:J300")))
Hi Vletm,

The solution couldn't have been any easier than that! That has fixed my issue - I'm going to adjust the master file and make sure all is good, but it seems to work perfectly.

Thank you so much!
Megan
 
Top