what function best to use

[Guest]

Hi I am new to excel and am trying to learn how to count line values from
another worksheet

Worksheet one
PO No Invoice No Line per order
901 1001 3
902 1002 0
903 1003 0

Worksheet two
Po No Inv no part no
901 1001 33mo333
901 1001 44mo444
901 1001 55mo555

 

[Biff]

Hi!

Try this:

=SUMPRODUCT(--(Sheet2!A$2:A$10=901),--(Sheet2!B$2:B$10=1001))

Biff

 

[Max]

One way ..

In Sheet1,

Put in C2:
=SUMPRODUCT((Sheet2!$A$2:$A$10=A2)*(Sheet2!$B$2:$B$10=B2))
Copy down

Adapt the ranges to suit, but note that entire col refs (A:A, B:B, etc)
cannot be used in SUMPRODUCT