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

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

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

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

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

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