Conditional Addition

[agarwaldvk]

I have a worksheet (say sheet1) where column "A" contains product name
such as Product1, Product2 etc in row 1 through to row 50. Column "B
contains quantities associated with each of these products, again i
row 1 through to row 50. The layout hence is something like this :-

(Sheet1)
A B
Product1 5
Product2 6

and so on.

On another sheet (say sheet2), cells(A1) through to cells(A10) ca
contain different product names like this :-


(Sheet2)
A1 - Product4
A2 - Product1
A3 - Product2
A4 - Product6
A5 - Product9
A6 - Product3
A7 - Product5
A8 - Product10
A9 - Product8
A10 - Product7

I want to add all the values in column B (from sheet1) but for onl
those products that appear in the range A1 through to A10 on sheet2?

Any suggestions?


Thanks in advance.


Best regards



Deepak Agarwa

 

[Frank Kabel]

Hi
try:
=SUMPRODUCT(--(ISNUMBER(MATCH(sheet1!$A$1:$A$100,sheet2!$A$1:$A$10,0)))
,sheet2!$B$1:$B$100)

 

[agarwaldvk]

Thanks Frank! You are a legend!


Best regards


Deepak Agarwa