Thread: sumproduct match

hi,

How can i do a match based on Column E region and H country name
to find the number from column A and B same country name within same region?

Pls see example.

thanks

If the source data (A:C) is a Pivot Table use the GETPIVOTDATA function.

If the source data is not a Pivot Table - can you confirm that each REGION_DESC_D code is repeated for each REGION_DESC_L code as implied by the sample.

=SUMPRODUCT(--($F$3:$F$69=B3),--($B$3:$B$69=F3),$C$3:$C$69)

i tried above code, but the problem is the REGION_DESC_D under each REGION_DESC_L cannot not repeated.


Pls see example file again for better understanding.

Can you answer the question raised in my prior post ?

Point being if the codes always repeat it's simply a case of:

H3:
=VLOOKUP($F3,OFFSET($B$3,MATCH(LOOKUP(REPT("Z",255),$E$3:$E3),$A$3:$A$22,0)-1,0,10,2),2,0)
copied down

or if you prefer to avoid the volatility of OFFSET

H3:
=VLOOKUP($F3,INDEX($B$3:$B$22,MATCH(LOOKUP(REPT("Z",255),$E$3:$E3),$A$3:$A$22,0)):$C$22,2,0)
copied down

If the codes don't always repeat then I'd suggest posting a further sample that accurately reflects the real file.

Again in the above I'm assuming A:C is not a Pivot Table - though the layout would imply it could be - if it is a Pivot Table you should be using GETPIVOTDATA function.