VLOOKUP and SUM based on an autofiltered list

[rlobera]

Hi!

I'm having trouble finding a solution to the mentioned problem. Maybe someone can help me!

In one sheet, I have a table where cells D5 to D100 have a series of repeated categories and a bunch of other columns that have additional information but not related to my problem. Then, in another sheet, I have a table (A7:B19) with the mentioned categories (not repetead) and each with a unique value. What I need is a formula that sums values in column B from the previous table according to the selected autofilter values in column D from the first sheet.

Would this be possible? I hope so!

Thanks a lot!
Rodrigo

 

[Marcelo Branco]

Not sure i understand your question.
See if this does what you need

Say you have this in Sheet1

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Categories​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Category1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Category2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Category3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

Category4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

Category5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

Category2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

Category3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

Category4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD]

Category5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD]

Category1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD]

Category2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

16​

[/TD]
[TD]

Category3​

[/TD]
[/TR]
</tbody>[/TABLE]


In Sheet2

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Categories​

[/TD]
[TD]

Value​

[/TD]
[TD][/TD]
[TD]

Result​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

Category1​

[/TD]
[TD]

10​

[/TD]
[TD][/TD]
[TD]

70​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

Category2​

[/TD]
[TD]

12​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

Category3​

[/TD]
[TD]

14​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

Category4​

[/TD]
[TD]

16​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

Category5​

[/TD]
[TD]

18​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Array formula in D8
=SUM(IF(ISNUMBER(MATCH(A8:A12,IF(SUBTOTAL(3,OFFSET(Sheet1!D5:D100,ROW(Sheet1!D5:D100)-ROW(Sheet1!D5),0,1)),Sheet1!D5:D100),0)),B8:B12))
Ctrl+Shift+Enter

So if you filter D5:D100 in Sheet1, say Category1 and Category4, you get

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

D
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Categories​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Category1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

Category4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

Category4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD]

Category1​

[/TD]
[/TR]
</tbody>[/TABLE]


And the formula in D8 of Sheet2 returns 26 (10+16) as desired (i suppose...)

Hope this helps

M.

 

[rlobera]

Awesome!

You're killing it!

 

[Aladin Akyurek]

I thought more like...

Control+shift+enter, not just enter:

=SUM(SUMIFS(B7:B19,A7:A19,IF(SUBTOTAL(3,OFFSET(Sheet1!$D$2,ROW(Sheet1!$D$2:$D$100)-ROW(Sheet1!$D$2)+1,0,1)),Sheet1!$D$2:$D$100)))

 

[Marcelo Branco]

It worked?

M.

 

[Aladin Akyurek]

It worked?

M.

The injection in the original post, which is "D5 to D100 have a series of repeated categories", made me think otherwise.

 

[Marcelo Branco]

The injection in the original post, which is "D5 to D100 have a series of repeated categories", made me think otherwise.

Sorry, Aladin.
I was not clear. I wanted to ask the OP (rlobera) if my formula worked for him.

M.