Excel using Unique() and Formula Auto-FIll

[MLHansen]

Hello Everyone,

I am using Unique on a table of about 10,000 entries. It returns a list and I want to then perform sumproducts and other functions against this list of unique values. This solution needs to be dynamic however as the number of unique entries will vary. Normally a table fixes this, add rows and the formulas will auto-populate. Unique() doesn't work in a table. So here's my question: How do I get a dynamically sized list of formulas's that is the length of the Unique() function?

Quick Example:
Fruit | Qty
Apple | 5
Pear | 6

Pear | 12
Banana | 7
Apple | 6

Unique(Table1[[Fruit]]) will return:
Apple | *I manually add a sumproduct for this and get* 11
Pear | *** 18
Banana | *** 7

If I add "Pineapple", Unique() returns 4 rows, but the sumproduct doesn't appear on row number 4. Any way to fix this? I'd love to just drop in values like a table. Normally with a table, you can paste in values to a column and all the formula's will extend to the size of the table. Obviously we can't house Unique() in a table, so I'm at a bit of a loss here on how to make this dynamically sized with Unique. 

Any help would be greatly appreciated!

[#2 · Accepted solution]

@MLHansen 

As variant two spills

left

=UNIQUE(Table1[[Fruit ]])

right

=SUMIFS(Table1[[ Qty]],Table1[[Fruit ]],UNIQUE(Table1[[Fruit ]]))

[#3]

@Sergei Baklan Has anyone offered you a kind word today? You sure deserve one. I cannot believe how simple and elegant your solution is. Thank you for sharing your knowledge with me. I can't wait for these functions to work in tables, it's so much nicer to read table references than array references. You're my hero for the day!

[#4]

Hi @MLHansen,

May you can try constructing the formula this way 

=SUMIF(Table1[Fruit],D2#,Table1[Qty])

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert | www.ExcelExciting.com

If you find the above solution resolved your query don't forget mark as Official Answer.

[#5]

If you find the above solution resolved your query don't forget mark as Official Answer.

[#6]

@MLHansen , you are welcome, glad to help

[#7]

@Faraz Shaikh Well both solutions work, that's awesome. I assume what I'm trying to do is get an equal length spill into the equations to generate the dynamic length correct? So any "spill" will work here. I think I'm gonna run with your solution as unique() likely has a larger overhead and my data set does take take time to iterate over. Thank you kindly!

[#8]

@MLHansen, glad to her your query is resolved & found our solution helpful. don't forget to marking the solution as best answers

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert | www.ExcelExciting.com

If you find the above solution resolved your query don't forget mark as Official Answer.

[#9]

If you find the above solution resolved your query don't forget mark as Official Answer.