Calculation with different condition in a cell

[LalitPandey87]

Hi All,

I have some data ex:

Category	Sub Category	Values
asd		2
asd		4
edf		2
asd		3
aec	a	2
edf	a	1
aef	a	2
qwe	a	1
aef	a	4

i need some formula or VBA which can do the calculation according to below condition:

1:- If Category contains value but sub cat. doesn't and there is only single entry for this then the output will be the value of the entry
2:- If Category contains value but sub cat. doesn't and there is more then one entry for this then the output will be the average of the values
3:- If Category and sub cat. both contains value and there is only single entry for this then the output will be the value of the entry
4:- If Category and sub cat. both contains value and there is more then one entry for this then the output will be the average of the values
5:- If Category and sub cat. both contain values and category are same but sub cat. are differ then the output will be the value of the entry

and Output will be like this:

Category	Sub Category	Values	OutPut
asd		2	3
asd		4	3
edf		2	2
asd		3	3
aec	a	2	2
edf	a	1	1
aef	a	2	3
qwe	a	1	1
aef	a	4	3

Thanks in Advance.

[Rick Rothstein]

Give this formula a try...

=SUMPRODUCT((A$2:A$10&B$2:B$10=A2&B2)*C$2:C$10)/SUMPRODUCT(1*(A$2:A$10&B$2:B$10=A2&B2))

[Rick Rothstein]

Give this formula a try...

=SUMPRODUCT((A$2:A$10&B$2:B$10=A2&B2)*C$2:C$10)/SUMPRODUCT(1*(A$2:A$10&B$2:B$10=A2&B2))

Actually, in thinking about it a little more, to avoid the possibility of a false match between those concatenations, use this modification of the formula instead...

=SUMPRODUCT((A$2:A$10&CHAR(1)&B$2:B$10=A2&CHAR(1)& B2)*C$2:C$10)/SUMPRODUCT(1*(A$2:A$10&CHAR(1)&B$2:B$10=A2&CHAR(1) &B2))

[Admin]

Hi

Assume your data in A2:C10, in D2 and copied down,

=AVERAGE(IF($A$2:$A$10=A2,IF($B$2:$B$10=B2,$C$2:$C $10)))

It's an array formula. Confirmed witH CTRL + SHIFT + ENTER

[LalitPandey87]

Thanks Rick and Admin. This is what i want all working fine for me.

[Admin]

Hi Lalit,

Thanks for the feedback