Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumif or sumproduct
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,S heet2!K2:K10001) but it doesn't work. Please help...thxs in advance. Paul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumif or sumproduct
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 "Paul" wrote: 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,S heet2!K2:K10001) but it doesn't work. Please help...thxs in advance. Paul |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumif or sumproduct
=SUMPRODUCT((Sheet2!A2:K10001=A7)*(Sheet2!B2:B1000 1=H7)*(Sheet2!K2:K10001))
-- HTH... Jim Thomlinson "Paul" wrote: 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,S heet2!K2:K10001) but it doesn't work. Please help...thxs in advance. Paul |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumif or sumproduct
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sumif or sumproduct
Try =SUMPRODUCT(--(Sheet2!A2:A10001="abc"),--(Sheet2!B2:B10001="art"),Sheet2!K2:K10001) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=532748 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SumIF or SumProduct | Excel Discussion (Misc queries) | |||
sumproduct or sumif | Excel Discussion (Misc queries) | |||
Sumif or Sumproduct | Excel Worksheet Functions | |||
SUMPRODUCT or SUMIF | Excel Worksheet Functions | |||
SUMPRODUCT or SUMIF or ... | Excel Worksheet Functions |