SUMIF(SUMPRODUCT(INDIRECT Formula Malfunction

[Leighbrek]

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.

 

[rahulshewale1]

Hi @Leighbrek ,

Please upload sample data


Regard
Rahul Shewale

 

[Leighbrek]

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

 

[vletm]

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]

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