Summing Unique Values

[Kenshin]

Good morning, another challenge maybe for the expert ihave my question and my requirement on file attachment

thank you

 

[bosco_yip]

Try,

E15, formula copy down :

=SUMPRODUCT(1/COUNTIF(OFFSET(OFFSET(B$1,MATCH(A15,A$2:A$10,0),),,,COUNTIF(A$2:A$10,A15),4),OFFSET(OFFSET(B$1,MATCH(A15,A$2:A$10,0),),,,COUNTIF(A$2:A$10,A15),4)&""),OFFSET(OFFSET(B$1,MATCH(A15,A$2:A$10,0),),,,COUNTIF(A$2:A$10,A15),4))

Regards
Bosco

 

[John Jairo V]

Hi to all!

This task is easier with PowerQuery. With formulas, is good for challenge, but have to use a lot of volatile functions.

When change the values into base list, simply right click into table and then refresh.

Check file. Blessings!

 

[Kenshin]

@Bosco it is great i can't think my self so amazing, but i have another request can you look at this? thanks

 

[bosco_yip]

@Bosco it is great i can't think my self so amazing, but i have another request can you look at this? thanks

Adopt the same logic, in C15 formula copy down :

=SUMPRODUCT(1/COUNTIF(OFFSET(OFFSET(B$1,MATCH(A15,A$2:A$10,0),),,MATCH(B15,B$1:E$1,0)-1,COUNTIF(A$2:A$10,A15)),OFFSET(OFFSET(B$1,MATCH(A15,A$2:A$10,0),),,MATCH(B15,B$1:E$1,0)-1,COUNTIF(A$2:A$10,A15))&""),OFFSET(OFFSET(B$1,MATCH(A15,A$2:A$10,0),),,MATCH(B15,B$1:E$1,0)-1,COUNTIF(A$2:A$10,A15)))

Regards
Bosco

 

[Kenshin]

Waow again my head explode very very thanks mannnnnnnnn....... i'm humble by your skill

 

[John Jairo V]

Hi @Kenshin (@bosco_yip, it's a pleasure match the post with you.)

First, sorry for my english.

When someone tries to help you, the least you should do is say something about that help.

Now, I leave another solution without volatile functions. Is an array formula, you must press Ctrl + Shift + Enter to get the right result (and not just enter).

=SUM((FREQUENCY(IF(($A$2:$A$10=A15)*($B$1:$E$1=B15),$B$2:$E$10),IF(($A$2:$A$10=A15)*($B$1:$E$1=B15),$B$2:$E$10))>0)*INDEX($B$2:$E$10,MATCH(A15,$A$2:$A$10,),MATCH(B15,$B$1:$E$1,)):INDEX($B$2:$E$11,MATCH(A15,$A$2:$A$11,)+COUNTIF($A$2:$A$10,A15),MATCH(B15,$B$1:$E$1,)))

Please comment! Blessings!

 

[azumi]

Hello John Jairo, that's another great formula thanks a lot man..... really realy appreciated

 

[John Jairo V]

Hi again!

Improving the formulae, could be like this (array formula too):

=SUM(IF(FREQUENCY(IF($A$2:$A$10=A15,MATCH(INDEX($B$2:$E$10,,MATCH(B15,$B$1:$E$1,)),INDEX($B$2:$E$10,,MATCH(B15,$B$1:$E$1,)),)),ROW($A$2:$A$10)-ROW($A$1)),INDEX($B$2:$E$10,,MATCH(B15,$B$1:$E$1,))))

Blessings!

 

[Khalid NGO]

John & Bosco you guys are Incredible
We always got surprised with your astounding formulas.

Blessing to you too.