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]

After some googling Ive seen how SUMPRODUCT users ORs so adapted my formula but still with no luck:

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

 

[Aladin Akyurek]

After some googling Ive seen how SUMPRODUCT users ORs so adapted my formula but still with no luck:

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

Does this...

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

help?

 

[crocco]

Unfortunately not. When I copy and paste straight from your post excel says theres an error and trys to correc tit.

What should I try?

Thanks for your help

 

[crocco]

If it helps:

QUART1,QUART2, etc will NEVER have the same infornmation as eachother on the same row


I have checked my named ranges are all the same size and cover the same row numbers

 

[Aladin Akyurek]

Unfortunately not. When I copy and paste straight from your post excel says theres an error and trys to correc tit.

What should I try?

Thanks for your help

Oops... I meant:

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

 

[crocco]

Still just getting #N/A error?

 

[Aladin Akyurek]

Still just getting #N/A error?

What results do we get with:

=ROWS(QAMT)

=ROWS(QUART1)

=ROWS(QUART2)

=ROWS(QUART3)

=ROWS(QUART4)

=ROWS(QPROP)

 

[crocco]

398 for all of them. I checked the ranges were definitely the same size already.

thanks

 

[Aladin Akyurek]

398 for all of them. I checked the ranges were definitely the same size already.

thanks

We still need to diagnose...

Care to post the exact references for each of the named ranges?