Sumproduct inside a sumproduct?

[charliew]

I am trying to sum the products of a cell in a few sheets, based on various arrays...needless to say, it wont work...

this is what i've got:

=SUMPRODUCT((CW!$B$2:$B$1000="Ext")*(CW!$I$2:$I$1000="RH")*(INT(CW!$D$2:$D$1000)=A85))*SUMPRODUCT((TC!$B$2:$B$1000="Ext")*(TC!$I$2:$I$1000="RH")*(INT(TC!$F$2:$F$1000)=A85))

the bold bit is on the other sheet and this is where i think im going wrong...???

thank you in anticipation!!!

 

[AlanY]

take out the 2nd sumproduct and try

=SUMPRODUCT((CW!$B$2:$B$1000="Ext")*(CW!$I$2:$I$1000="RH")*(INT(CW!$D$2:$D$1000)=A85)*(TC!$B$2:$B$1000="Ext")*(TC!$I$2:$I$1000="RH")*(INT(TC!$F$2:$F$1000)=A85))

 

[Aladin Akyurek]

Perhaps you mean:

=SUMPRODUCT(
     ((CW!$B$2:$B$1000="Ext")+(TC!$B$2:$B$1000="Ext"))*
     ((CW!$I$2:$I$1000="RH")+(TC!$I$2:$I$1000="RH"))*
     (INT(CW!$D$2:$D$1000=A85)+(TC!$F$2:$F$1000=A85)))

 

[charliew]

Neither of those are working

=SUM(SUMPRODUCT((CW!$B$2:$B$1000="Ext")*(CW!$I$2:$I$1000=F1)*(INT(CW!$D$2:$D$1000)=A85)),SUMPRODUCT((TC!$B$2:$B$1000="Ext")*(TC!$I$2:$I$1000=F1)*(INT(TC!$F$2:$F$1000)=A85)))

ive managed the above that is giving us a more believable number but it's still 100% correct!

 

[Aladin Akyurek]

Neither of those are working

=SUM(SUMPRODUCT((CW!$B$2:$B$1000="Ext")*(CW!$I$2:$I$1000=F1)*(INT(CW!$D$2:$D$1000)=A85)),SUMPRODUCT((TC!$B$2:$B$1000="Ext")*(TC!$I$2:$I$1000=F1)*(INT(TC!$F$2:$F$1000)=A85)))

ive managed the above that is giving us a more believable number but it's still 100% correct!

You didn't imply a sum at all. It's hard to interpret: "giving us a more believable number but it's still 100% correct!" because of "but" in your statement.

 

[charliew]

You didn't imply a sum at all. It's hard to interpret: "giving us a more believable number but it's still 100% correct!" because of "but" in your statement.

I'm really sorry, i missed a "not" out. It's supposed to read "giving us a more believable number but it's still NOT 100% correct!"

I think, we have managed to get this to work now tho - thank you for all your help.

On a separate issue i have a formula that is working perfectly in my "CW" sheet, but the exact same formula is throwing out a [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=value]#value [/URL] ! error in my "TC" sheet. The cells are all in the same places, the cells appear to be formatted the same...so i cant figure out why its working in one sheet and not the other??!

=SUMPRODUCT(($B$2:$B$1000="Ext")*($I$2:$I$1000="CS")*(INT($D$2:$D$1000)=AD35))

this variation kicks out a [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=value]#value [/URL] ! error but when i change the D column to F it works...! the date in D and F is both date and time just different dates and times but D column doesnt work!?

 

[Aladin Akyurek]

What does

=ISUMBER(D2)

return?

 

[charliew]

#name ?

 

[charliew]

=ISNUMBER(D2) returns "TRUE"

 

[Aladin Akyurek]

=ISNUMBER(D2) returns "TRUE"

Do you have formulas in the D range or in any of the relevant ranges?