Nesting sumproduct in multiple sheets

[Brad Johnstun]

Hi,

I have two sheets I am working with and I need to nest a sumproduct fomula and I can't seem to get this working..maybe I need to use a different formula function??

The cells I want the data to populate are on sheet 1 (Master 1)

My first array; sheet 2 (bass) a5:a5000 = sheet 1 (Master 1) a2
My second array; sheet 2 (bass) b5:b5000 = sheet 1 (Master 1) b2
if true..sum data from sheet 2 (bass) r5:r5000

This keeps giving me N/A. My formula is below.
=SUMPRODUCT((BASS!$A$5:$A$5000='MASTER 1'!A2)*(BASS!$B$5:$B$5000='MASTER 1'!B2)*BASS!$R$5:$R$5000)

Please help.

Thanks,
Brad

 

[T. Valko]

Hi,

I have two sheets I am working with and I need to nest a sumproduct fomula and I can't seem to get this working..maybe I need to use a different formula function??

The cells I want the data to populate are on sheet 1 (Master 1)

My first array; sheet 2 (bass) a5:a5000 = sheet 1 (Master 1) a2
My second array; sheet 2 (bass) b5:b5000 = sheet 1 (Master 1) b2
if true..sum data from sheet 2 (bass) r5:r5000

This keeps giving me N/A. My formula is below.
=SUMPRODUCT((BASS!$A$5:$A$5000='MASTER 1'!A2)*(BASS!$B$5:$B$5000='MASTER 1'!B2)*BASS!$R$5:$R$5000)

Please help.

Thanks,
Brad

There's nothing wrong with your formula.

Are there any #N/A errors already in any of the ranges?