Sumproduct formula help please..or should it be a different formula?

[Marq]

Below is a sumproduct formula I made which is giving me "0" result...which is a lot better than an "error" result so I assume im in the right direction.

I am wanting the formula to read as follows <the formula starts in cell H3 on "Unique Items" tab and is drug down to H379>:

Look at range A3 through A379 and then find the matching entries in Sheet1 range AL3 through AL2962 and whichever SHEET1 entry matches any entry on UNIQUE ITEMS A3 through A379, then add the hours from range Q3 through Q2962 on sheet 1.

So for example if one of my unique items on UNIQUE ITEMS tab is 63171844-0370 4531564697-00800 then the formula is to find all of the 63171844-0370 4531564697-00800's in column AL of Sheet1 and return the sum of the hours in range Q3 through Q2962 on Sheet1 that match the two criteria's of 63171844-0370 4531564697-00800........so out of the 15,465 hrs in range Q3 through Q2962, 227 hrs of them should show up in the cell H3 UNIQUE ITEMS tab.

=SUMPRODUCT($A$3:$A$379=A36)*(Sheet1!$AL$3:$AL$2962='UNIQUE ITEMS'!A36)*(Sheet1!$Q$3:$Q$2962)

i use the formula above and get ZERO result when i should be getting values greater than zero

 

[GraH]

Hi, I find it strange this formula is returning a value, as the ranges in the arguments are not the same size.
Is this part required $A$3:$A$379=A36? Doesn't a SUMIFS () work?

 

[Marq]

Hi, I find it strange this formula is returning a value, as the ranges in the arguments are not the same size.
Is this part required $A$3:$A$379=A36? Doesn't a SUMIFS () work?

A3:A379 is my range of unique items...thats the main list.....everything else in the formula bounces off that. Row 36 was just where i copied and pasted from to put in my post....so its saying find in Sheet1 whatever entries match A36...and sum up those entries values in column Q of Sheet1

 

[Marq]

Hi, I find it strange this formula is returning a value, as the ranges in the arguments are not the same size.
Is this part required $A$3:$A$379=A36? Doesn't a SUMIFS () work?

im not sure how to write the sumif's..ive tried a few different variations with error returns....the sumproduct got me to show zero value so i was sticking with that.

 

[GraH]

A3:A379 is my range of unique items...thats the main list.....everything else in the formula bounces off that. Row 36 was just where i copied and pasted from to put in my post....so its saying find in Sheet1 whatever entries match A36...and sum up those entries values in column Q of Sheet1

I got that, but why do you include it in your formula? Doesn't this work? SUMPRODUCT((Sheet1!$AL$3:$AL$2962='UNIQUE ITEMS'!A36)*(Sheet1!$Q$3:$Q$2962) )

 

[Marq]

I got that, but why do you include it in your formula? Doesn't this work? SUMPRODUCT((Sheet1!$AL$3:$AL$2962='UNIQUE ITEMS'!A36)*(Sheet1!$Q$3:$Q$2962) )

Yes!..That works!!..thank you!

 

[GraH]

Then probably SUMIF(Sheet1!$AL$3:$AL$2962, 'UNIQUE ITEMS'!A36, Sheet1!$Q$3:$Q$2962) also works.