Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif by multiple condition
Hi,
I want find out summary with sumif formula for the following datas: 1 A1 B1 C1 D1 E1 2 STAFF # DEP SAL OT ALOW 3 4 101 SALES $400 $75 $200 5 102 ACCT $500 $100 $250 6 103 ADMIN $350 $50 $100 7 104 PUR $350 $- $150 8 105 ACCT $450 $- $150 9 106 ADMIN $300 $75 $125 10 107 PUR $400 $100 $150 11 12 SUMMARY: 13 14 ALOW ACCT ? 15 OT ACCT ? 16 SAL ACCT ? 17 ALOW ADMIN ? 18 OT ADMIN ? 19 SAL ADMIN ? 20 ALOW PUR ? 21 OT PUR ? 22 SAL PUR ? 23 ALOW SALES ? 24 OT SALES ? 25 SAL SALES ? Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif by multiple condition
You can't use sumif for more than one condition. Your choices are a Pivot Table,
or Sumproduct. If you need more help, post back with specifics on what you want to do. -- Regards, Fred "ajay" wrote in message ... Hi, I want find out summary with sumif formula for the following datas: 1 A1 B1 C1 D1 E1 2 STAFF # DEP SAL OT ALOW 3 4 101 SALES $400 $75 $200 5 102 ACCT $500 $100 $250 6 103 ADMIN $350 $50 $100 7 104 PUR $350 $- $150 8 105 ACCT $450 $- $150 9 106 ADMIN $300 $75 $125 10 107 PUR $400 $100 $150 11 12 SUMMARY: 13 14 ALOW ACCT ? 15 OT ACCT ? 16 SAL ACCT ? 17 ALOW ADMIN ? 18 OT ADMIN ? 19 SAL ADMIN ? 20 ALOW PUR ? 21 OT PUR ? 22 SAL PUR ? 23 ALOW SALES ? 24 OT SALES ? 25 SAL SALES ? Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif by multiple condition
Hi Fred,
Can you please help me how to apply the function "sumproduct" inorder to get this result. Thanks, Ajay "Fred Smith" wrote: You can't use sumif for more than one condition. Your choices are a Pivot Table, or Sumproduct. If you need more help, post back with specifics on what you want to do. -- Regards, Fred "ajay" wrote in message ... Hi, I want find out summary with sumif formula for the following datas: 1 A1 B1 C1 D1 E1 2 STAFF # DEP SAL OT ALOW 3 4 101 SALES $400 $75 $200 5 102 ACCT $500 $100 $250 6 103 ADMIN $350 $50 $100 7 104 PUR $350 $- $150 8 105 ACCT $450 $- $150 9 106 ADMIN $300 $75 $125 10 107 PUR $400 $100 $150 11 12 SUMMARY: 13 14 ALOW ACCT ? 15 OT ACCT ? 16 SAL ACCT ? 17 ALOW ADMIN ? 18 OT ADMIN ? 19 SAL ADMIN ? 20 ALOW PUR ? 21 OT PUR ? 22 SAL PUR ? 23 ALOW SALES ? 24 OT SALES ? 25 SAL SALES ? Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif by multiple condition
hi,
you can use the sumproduct function to sum with two or more conditions; a sample could be: =sumproduct(--(a2:a100="1st condition")*(b2:b100="2nd conditional"),(c2:c100)) 1st and 2nd condtional can be a cell or cells and C column has the data that you need to sum. adjust the ranges for your needs and have fun. hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "ajay" escreveu: Hi Fred, Can you please help me how to apply the function "sumproduct" inorder to get this result. Thanks, Ajay "Fred Smith" wrote: You can't use sumif for more than one condition. Your choices are a Pivot Table, or Sumproduct. If you need more help, post back with specifics on what you want to do. -- Regards, Fred "ajay" wrote in message ... Hi, I want find out summary with sumif formula for the following datas: 1 A1 B1 C1 D1 E1 2 STAFF # DEP SAL OT ALOW 3 4 101 SALES $400 $75 $200 5 102 ACCT $500 $100 $250 6 103 ADMIN $350 $50 $100 7 104 PUR $350 $- $150 8 105 ACCT $450 $- $150 9 106 ADMIN $300 $75 $125 10 107 PUR $400 $100 $150 11 12 SUMMARY: 13 14 ALOW ACCT ? 15 OT ACCT ? 16 SAL ACCT ? 17 ALOW ADMIN ? 18 OT ADMIN ? 19 SAL ADMIN ? 20 ALOW PUR ? 21 OT PUR ? 22 SAL PUR ? 23 ALOW SALES ? 24 OT SALES ? 25 SAL SALES ? Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif by multiple condition
=SUMPRODUCT(--($B$3:$B$10=B14),INDEX($C$3:$E$10,0,MATCH(A14,$C$2 :$E$2,0)))
copy down "ajay" wrote: Hi, I want find out summary with sumif formula for the following datas: 1 A1 B1 C1 D1 E1 2 STAFF # DEP SAL OT ALOW 3 4 101 SALES $400 $75 $200 5 102 ACCT $500 $100 $250 6 103 ADMIN $350 $50 $100 7 104 PUR $350 $- $150 8 105 ACCT $450 $- $150 9 106 ADMIN $300 $75 $125 10 107 PUR $400 $100 $150 11 12 SUMMARY: 13 14 ALOW ACCT ? 15 OT ACCT ? 16 SAL ACCT ? 17 ALOW ADMIN ? 18 OT ADMIN ? 19 SAL ADMIN ? 20 ALOW PUR ? 21 OT PUR ? 22 SAL PUR ? 23 ALOW SALES ? 24 OT SALES ? 25 SAL SALES ? Thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumif by multiple condition
Hi,
I got the rquired result after applying the following formula. Thank you so much. Ajay "Teethless mama" wrote: =SUMPRODUCT(--($B$3:$B$10=B14),INDEX($C$3:$E$10,0,MATCH(A14,$C$2 :$E$2,0))) copy down "ajay" wrote: Hi, I want find out summary with sumif formula for the following datas: 1 A1 B1 C1 D1 E1 2 STAFF # DEP SAL OT ALOW 3 4 101 SALES $400 $75 $200 5 102 ACCT $500 $100 $250 6 103 ADMIN $350 $50 $100 7 104 PUR $350 $- $150 8 105 ACCT $450 $- $150 9 106 ADMIN $300 $75 $125 10 107 PUR $400 $100 $150 11 12 SUMMARY: 13 14 ALOW ACCT ? 15 OT ACCT ? 16 SAL ACCT ? 17 ALOW ADMIN ? 18 OT ADMIN ? 19 SAL ADMIN ? 20 ALOW PUR ? 21 OT PUR ? 22 SAL PUR ? 23 ALOW SALES ? 24 OT SALES ? 25 SAL SALES ? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup with multiple condition, but one condition to satisfy is en | Excel Worksheet Functions | |||
Sumif, having two condition | Excel Worksheet Functions | |||
sumif with or< condition | Excel Discussion (Misc queries) | |||
Multiple Condition Sumif Formula | Excel Worksheet Functions | |||
Is it possible to specify multiple condition with SUMIF? | Excel Worksheet Functions |