Dear All

My Data is like this;

Name City Total Sum Average
ABC Delhi 7.8 24 6
ABC Delhi 6.8
ABC Delhi 5.23
ABC Delhi 4.23
ABC Noida 12 12 12
DEF Noida 24 47 24
DEF Noida 23
GHI Faridabad 24.3 24 24
JKL Mumbai 12 151 30
JKL Mumbai 56
JKL Mumbai 48
JKL Mumbai 12
JKL Mumbai 23
JKL Naskik 15 29 15
JKL Naskik 14
MNO Chennai 12 25 13
MNO Chennai 13
MNO Hyderabad 15 15 15
PQR Noida 12 12 12
PQR Delhi 14 26 13
PQR Delhi 12
STU Hyderabad 16 35 18
STU Hyderabad 19
VWX Gurgaon 17 35 18
VWX Gurgaon 18
VWX Delhi 21 21 21
XYZ Gurgaon 22 45 23
XYZ Gurgaon 23
XYZ Noida 26 26 26
XYZ Delhi 28 87 29
XYZ Delhi 29
XYZ Delhi 30


I want to Sum Column C Based on Column A as well as Column B.

For Example: In A2 to A5 (ABC) total =24 Becaue City is Different .In
A6(ABC) Total=12 City is Different and so on

Write now i am using SUMIF($A$2:$A$33,A2,$C$2:$C$33)
But i Give me the Total of All ABC i.e 36
Column D is the Total Which is My Requirement.

2) After Getting the total in Column D. Now I want the Average in Column E
as given in Example:

Say A2:A5 total= 6,Now i want to Divide 24/4 Result is 6
A6 has only one data then 12/1 Result is 12

And so on

Any type of Help is Appreciate

Thanks in Advance

=sumproduct((a2:a22="abc")*(b2:b22="noida")*c2:c22 )

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Pai" wrote in message
...
Dear All

My Data is like this;

Name City Total Sum Average
ABC Delhi 7.8 24 6
ABC Delhi 6.8
ABC Delhi 5.23
ABC Delhi 4.23
ABC Noida 12 12 12
DEF Noida 24 47 24
DEF Noida 23
GHI Faridabad 24.3 24 24
JKL Mumbai 12 151 30
JKL Mumbai 56
JKL Mumbai 48
JKL Mumbai 12
JKL Mumbai 23
JKL Naskik 15 29 15
JKL Naskik 14
MNO Chennai 12 25 13
MNO Chennai 13
MNO Hyderabad 15 15 15
PQR Noida 12 12 12
PQR Delhi 14 26 13
PQR Delhi 12
STU Hyderabad 16 35 18
STU Hyderabad 19
VWX Gurgaon 17 35 18
VWX Gurgaon 18
VWX Delhi 21 21 21
XYZ Gurgaon 22 45 23
XYZ Gurgaon 23
XYZ Noida 26 26 26
XYZ Delhi 28 87 29
XYZ Delhi 29
XYZ Delhi 30


I want to Sum Column C Based on Column A as well as Column B.

For Example: In A2 to A5 (ABC) total =24 Becaue City is Different .In
A6(ABC) Total=12 City is Different and so on

Write now i am using SUMIF($A$2:$A$33,A2,$C$2:$C$33)
But i Give me the Total of All ABC i.e 36
Column D is the Total Which is My Requirement.

2) After Getting the total in Column D. Now I want the Average in Column E
as given in Example:

Say A2:A5 total= 6,Now i want to Divide 24/4 Result is 6
A6 has only one data then 12/1 Result is 12

And so on

Any type of Help is Appreciate

Thanks in Advance

In D2:
=SUMPRODUCT(--($A$2:$A$33=A2),--($B$2:$B$33=B2),$C$2:$C$33)
In E2
=D2/SUMPRODUCT(--($A$2:$A$33=A2),--($B$2:$B$33=B2))

Adjust ranges and copy as required!

Regards,
Stefi

€žPai€� ezt Ã*rta:

Dear All

My Data is like this;

Name City Total Sum Average
ABC Delhi 7.8 24 6
ABC Delhi 6.8
ABC Delhi 5.23
ABC Delhi 4.23
ABC Noida 12 12 12
DEF Noida 24 47 24
DEF Noida 23
GHI Faridabad 24.3 24 24
JKL Mumbai 12 151 30
JKL Mumbai 56
JKL Mumbai 48
JKL Mumbai 12
JKL Mumbai 23
JKL Naskik 15 29 15
JKL Naskik 14
MNO Chennai 12 25 13
MNO Chennai 13
MNO Hyderabad 15 15 15
PQR Noida 12 12 12
PQR Delhi 14 26 13
PQR Delhi 12
STU Hyderabad 16 35 18
STU Hyderabad 19
VWX Gurgaon 17 35 18
VWX Gurgaon 18
VWX Delhi 21 21 21
XYZ Gurgaon 22 45 23
XYZ Gurgaon 23
XYZ Noida 26 26 26
XYZ Delhi 28 87 29
XYZ Delhi 29
XYZ Delhi 30


I want to Sum Column C Based on Column A as well as Column B.

For Example: In A2 to A5 (ABC) total =24 Becaue City is Different .In
A6(ABC) Total=12 City is Different and so on

Write now i am using SUMIF($A$2:$A$33,A2,$C$2:$C$33)
But i Give me the Total of All ABC i.e 36
Column D is the Total Which is My Requirement.

2) After Getting the total in Column D. Now I want the Average in Column E
as given in Example:

Say A2:A5 total= 6,Now i want to Divide 24/4 Result is 6
A6 has only one data then 12/1 Result is 12

And so on

Any type of Help is Appreciate

Thanks in Advance

Awesome Stefi Awesome

Greeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee eeeeeeeeeeeeeeeeet


Pai

"Stefi" wrote:

In D2:
=SUMPRODUCT(--($A$2:$A$33=A2),--($B$2:$B$33=B2),$C$2:$C$33)
In E2
=D2/SUMPRODUCT(--($A$2:$A$33=A2),--($B$2:$B$33=B2))

Adjust ranges and copy as required!

Regards,
Stefi

€žPai€� ezt Ã*rta:

Dear All

My Data is like this;

Name City Total Sum Average
ABC Delhi 7.8 24 6
ABC Delhi 6.8
ABC Delhi 5.23
ABC Delhi 4.23
ABC Noida 12 12 12
DEF Noida 24 47 24
DEF Noida 23
GHI Faridabad 24.3 24 24
JKL Mumbai 12 151 30
JKL Mumbai 56
JKL Mumbai 48
JKL Mumbai 12
JKL Mumbai 23
JKL Naskik 15 29 15
JKL Naskik 14
MNO Chennai 12 25 13
MNO Chennai 13
MNO Hyderabad 15 15 15
PQR Noida 12 12 12
PQR Delhi 14 26 13
PQR Delhi 12
STU Hyderabad 16 35 18
STU Hyderabad 19
VWX Gurgaon 17 35 18
VWX Gurgaon 18
VWX Delhi 21 21 21
XYZ Gurgaon 22 45 23
XYZ Gurgaon 23
XYZ Noida 26 26 26
XYZ Delhi 28 87 29
XYZ Delhi 29
XYZ Delhi 30


I want to Sum Column C Based on Column A as well as Column B.

For Example: In A2 to A5 (ABC) total =24 Becaue City is Different .In
A6(ABC) Total=12 City is Different and so on

Write now i am using SUMIF($A$2:$A$33,A2,$C$2:$C$33)
But i Give me the Total of All ABC i.e 36
Column D is the Total Which is My Requirement.

2) After Getting the total in Column D. Now I want the Average in Column E
as given in Example:

Say A2:A5 total= 6,Now i want to Divide 24/4 Result is 6
A6 has only one data then 12/1 Result is 12

And so on

Any type of Help is Appreciate

Thanks in Advance