List non-blank cell values vertically and sum them up

[kanadaaa]

	A	B	C	D
1	1	a		c
2	2	b	a
3	3	c		​

I want to extract all non-blank data from the cells in B1:D3, list them vertically without duplicates, and on the right column get the sum of (101-rank) for each extracted value, e.g.

a	199 (=(101-1)+(101-2))
b	99 (=101-2)
c	198 (=(101-3)+(101-1))

where the "rank" is each number in column A.

How can I do this using formulas? I would appreciate your help.

 

[hagia_sofia]

Hello, if you have access to GROUPBY function then maybe:

=LET(
a,A1:A3,
b,B1:D3,
c,101-a,
d,IF(NOT(ISBLANK(b)),c,NA()),
e,TOCOL(b,1),
f,TOCOL(d,2),
GROUPBY(e,f,SUM,,0))

 

[Cubist]

You can shorten to:

Book1
	A	B	C	D	E	F	G
1	1	a		c		a	199
2	2	b	a			b	99
3	3	c				c	198
Sheet2

Cell Formulas
Range	Formula
F1:G3	F1	=GROUPBY(TOCOL(B1:D3,1),TOCOL(IFS(B1:D3<>"",101-A1:A3),2),SUM,,0)
Dynamic array formulas.