How to select a part of a range, which is output of a formula

February 1, 2022

I seem to be really bad at writing readable titles 🙂

Here’s the situation:
I have a list of names and values, and I would like to calculate the sum of the values, corresponding to every name:

Name  Value
   a      1
   b      2
   a      3
   c      4
   a      5
   b      6
   c      7

This looks really simple:

for 'a' : =SUMIF($A$2:$A$8,A2,$B$2:$B$8)
for 'b' : =SUMIF($A$2:$A$8,A3,$B$2:$B$8)
for 'c' : =SUMIF($A$2:$A$8,A4,$B$2:$B$8)

Oops, that’s wrong, because A4 does not contain ‘c’, it contains ‘a’ again.

No sweat: we can use the UNIQUE() formula, which puts the values ‘a’, ‘b’ and ‘c’ in column F (=UNIQUE(A2:A8)).
Once we have this, we can easily use this formula:

for 'a' : =SUMIF($A$2:$A$8,F2,$B$2:$B$8)
for 'b' : =SUMIF($A$2:$A$8,F3,$B$2:$B$8)
for 'c' : =SUMIF($A$2:$A$8,F4,$B$2:$B$8)

This is correct, but it means that I must use the F column as a reference.
I don’t want that, I want something like:

for 'a' : =SUMIF($A$2:$A$8,UNIQUE(A2:A8).getElement(1),$B$2:$B$8)
for 'b' : =SUMIF($A$2:$A$8,UNIQUE(A2:A8).getElement(2),$B$2:$B$8)
for 'c' : =SUMIF($A$2:$A$8,UNIQUE(A2:A8).getElement(3),$B$2:$B$8)

This, obviously, makes no sense, as getElement() does not exist.
How can I access a part of a UNIQUE() result?

This is how my Excel sheet looks like:

>Solution :

I’m unsure if you want two columns or not, but with ms365:

Formula in D2:

=CHOOSE({1,2},UNIQUE(A2:A8),SUMIF(A2:A8,UNIQUE(A2:A8),B2:B8))

And to answer the part on the ‘.GetElement’, I’d say that INDEX() is a very good translation and allows you to slice into an array and pick any of your liking. I don’t think it’s your goal, but if say you are interested in only the 2nd row of the whole array you can use: =INDEX(PreviousFormula, 2, 0)