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 :cheers: