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

	D​
4​	Categories​
5​	Category1​
6​	Category2​
7​	Category3​
8​	Category4​
9​	Category5​
10​	Category2​
11​	Category3​
12​	Category4​
13​	Category5​
14​	Category1​
15​	Category2​
16​	Category3​

<tbody>
</tbody>

In Sheet2

	A​	B​	C​	D​
7​	Categories​	Value​		Result​
8​	Category1​	10​		70​
9​	Category2​	12​
10​	Category3​	14​
11​	Category4​	16​
12​	Category5​	18​

<tbody>
</tbody>

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

	D ​
4​	Categories​
5​	Category1​
8​	Category4​
12​	Category4​
14​	Category1​

<tbody>
</tbody>

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.