vivi wrote:
Thanks for your reply, but the sumproduct function cant help my
problem unfortunately as I am not mutiplying numbers.

Sumproduct is useful not only to multiply numbers...


For example

Book1: Cell A1 = Zoe, Cell A2=David, Cell A3=John, Cell A4=Zoe
Cell B1 = 20, Cell B2=10, Cell B3 = 30, Cell B4=25

Book2 Cell A1=David, Cell A2=Zoe and Cell A3=John
Then in Cell B1 to B3 a formula
=sumif('Book1'!A:A,'Book2'!A1,'Book1'!B:B)

Therefore Cell B1 should have the result of 10; Cell B2 is 45 and
Cell B3 is 30

Therefore sumproduct does not help, is there another function that I
can use?



=SUMPRODUCT(('Book1'!A1:A30='Book2'!A1)*('Book1'!B 1:B30))

the only limitation to use of SUMPRODUCT is that you cannot use entire
columns/rows...


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy