Sumif or sumproduct

[Paul]

Hi,

Newbie question: On sheet 2, I have a table from A1:K5000:

A (Project ID) B (Department) ....... K (Sum of hours)
abc art 15
bac prog 22
cba art 32

On sheet 1, I'm creating a sumup table and want to sum automatically the
hours from column K on sheet 2 from two criterias that i choose in sheet 1.

Sum all hours from project ABC of the art department from the data of Sheet 2:

Tried many things
like...=SUMPRODUCT(Sheet2!A2:K10001=A7,Sheet1!H7,Sheet2!K2:K10001) but it
doesn't work.

Please help...thxs in advance.

Paul

 

[daddylonglegs]

Try

=SUMPRODUCT(--(Sheet2!A2:A10001="abc"),--(Sheet2!B2:B10001="art"),Sheet2!K2:K10001)

 

[Toppers]

Assuming this is on Sheet1 with A & B also on Sheet1: if not add sheet prefix
as per last array item:

=sumproduct(--(A1:a5000="abc"),--(b1:b5000="art"),--(sheet2!k1:k5000))

You can replace "abc" and "art" by cells which contain "abc" and "art"

=sumproduct(--(A1:a5000=H1),--(b1:b5000=M1),--(sheet2!k1:k5000))

h1="abc"
M1="art"

hth

 

[Jim Thomlinson]

=SUMPRODUCT((Sheet2!A2:K10001=A7)*(Sheet2!B2:B10001=H7)*(Sheet2!K2:K10001))

 

[Carim]

Hi Paul,

....=SUMPRODUCT(--(Sheet2!A2:K10001=A7),--(Sheet1!H2:H10001),Sheet2!K2:K10001)

Watch 2 things ...
1. Range criteria of column H should be of the same size as other
ranges ...
2. When referring to non numeric columns, use the unary operator --

HTH
Cheers
Carim