Search code examples
excelexcel-formulauniqueexcel-365

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

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

Solution

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

    enter image description here

    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). The '0' will ensure that we return all column that make up that row and can be left out if the array is only made of a single column in the first place.