Summing up a range based on another range?

[Pauljj]

I need to sum up the values in column L if the product codes in column C match a range of product codes in range P20 to P100

I have named the range of P20 to P100 as 'INDOCHINA'

I have tried various SUMPRODUCT formulas including this one

=SUMPRODUCT(--ISNA(MATCH(C7:C22,{"INDOCHINA"})),L7:L22)

This just gave me the entire total and not just the product codes in the named range

Any ideas please ?

 

[Mike Blackman]

The formula won't return the product codes for you as well, you either need to go down the VBA route or use a pivot table if you want it to show you which codes have matched and the SUM.

 

[Pauljj]

Hi there I don't need it to show which have matched, I am trying to sum up one column based on the what codes are in the range of INDOCHINA

 

[ayazgreat]

Could you please post your table here ?

 

[Mike Blackman]

Maybe this?

=SUMPRODUCT(--(ISNUMBER(MATCH(C7:C22,INDOCHINA,0))),L7:L22)

 

[Aladin Akyurek]

I need to sum up the values in column L if the product codes in column C match a range of product codes in range P20 to P100

I have named the range of P20 to P100 as 'INDOCHINA'

I have tried various SUMPRODUCT formulas including this one

=SUMPRODUCT(--ISNA(MATCH(C7:C22,{"INDOCHINA"})),L7:L22)

This just gave me the entire total and not just the product codes in the named range

Any ideas please ?

Paul,

If INDOCHINA is the name of a range, the formula should run as:

=SUMPRODUCT(--ISNA(MATCH(C7:C22,INDOCHINA,0)),L7:L22)

Equivalently...

=SUMPRODUCT(C7:C22,"<>"&INDOCHINA,L7:L22)

 

[Pauljj]

Unfortunatley this didn't seem to work for me.

Just to clarify, at present the range INDOCHINA, contains three product codes It should be summing up L7 to L22 iwhere those three product codes exist in C7 to C22

Cheers

 

[Pauljj]

I've just realised what the problem is, so please ignore, may thanks for your help much appreciated


Paul

 

[Mike Blackman]

My formula should have done that, did you try it?

 

[Aladin Akyurek]

I've just realised what the problem is, so please ignore, may thanks for your help much appreciated


Paul

=SUMPRODUCT(--ISNA(MATCH(C7:C22,INDOCHINA,0)),L7:L22)

=SUMPRODUCT(C7:C22,"<>"&INDOCHINA,L7:L22)

are formulated as No Match. If you intend the opposite:

=SUMPRODUCT(--ISNUMBER(MATCH(C7:C22,INDOCHINA,0)),L7:L22)

=SUMPRODUCT(C7:C22,INDOCHINA,L7:L22)