SUMPRODUCT use

[crocco]

I think this is an incorrect use of sumproduct but im trying to total a column (QAMT) pulling the result to relevant column headings (B2,B3, etc Months of the years) based on whether the month are in any of four possible columns (QUART1,QUART2, etc) and there is a match in a property number column as well (QPROP) on a spearate table.



=SUMPRODUCT((QAMT),(OR(QUART1=B2,QUART2=B2,QUART3=B2,QUART4=B2))*(QPROP=A6))


I am assuming that SUMPRODUCT doesnt like my use of OR? How do I get around this?

Thanks in advance

 

[crocco]

QAMT =Quarterly!$F$3:$F$400
QPROP =Quarterly!$A$3:$A$400
QUART1 =Quarterly!$B$3:$B$400
QUART2 =Quarterly!$C$3:$C$400
QUART3 =Quarterly!$D$3:$D$400
QUART4 =Quarterly!$E$3:$E$400


Is this what you're after?

 

[crocco]

QAMT =Quarterly!$F$3:$F$400
QPROP =Quarterly!$A$3:$A$400
QUART1 =Quarterly!$B$3:$B$400
QUART2 =Quarterly!$C$3:$C$400
QUART3 =Quarterly!$D$3:$D$400
QUART4 =Quarterly!$E$3:$E$400


Is this what you're after?

 

[Aladin Akyurek]

QAMT =Quarterly!$F$3:$F$400
QPROP =Quarterly!$A$3:$A$400
QUART1 =Quarterly!$B$3:$B$400
QUART2 =Quarterly!$C$3:$C$400
QUART3 =Quarterly!$D$3:$D$400
QUART4 =Quarterly!$E$3:$E$400


Is this what you're after?

Yes. Maybe we should have a look at the file itself...

 

[crocco]

WHats the best way for me to show it to you?

 

[crocco]

I've figured this out. One of my values in QAMT was an N/A# but the error wasnt being ignored because it was a pasted value. does that make sense?

thanks for your help

 

[Aladin Akyurek]

I've figured this out. One of my values in QAMT was an N/A# but the error wasnt being ignored because it was a pasted value. does that make sense?

thanks for your help

Yes, absolutely. Remove that #N/A from QAMT in order to invoke:

=SUMPRODUCT(
 QAMT,
 --((QUART1=B2)+(QUART2=B2)+(QUART3=B2)+(QUART4=B2)>0),
 --(QPROP=A6))

Otherwise, invoke...
Control+shift+enter, not just enter:

=SUM(
 IF((QUART1=B2)+(QUART2=B2)+(QUART3=B2)+(QUART4=B2)>0,
 IF(QPROP=A6,
 IF(ISNUMBER(QAMT),QAMT))))