Sum vlookup results using filtered lookup criteria

[junbuggle]

Hello,

I was hoping someone might be kind enough to help me with a vlookup question.

I'm trying to sum the results of a vlookup, where the lookup criteria are: (1) visible values in a filtered list; and (2) a static value.

So based on the screenshot below, if I filter Table A for George and John, in Row 2 I should get 9 for March, and 11 for April.

So 'George' and 'John' are the visible values in the filtered list. And 'March' and 'April' are static values, and which form a second criterion.

Could someone please help me? I'd be so grateful.

I've looked up combining index, match, and subtotal with vlookups, but am at a complete loss...


Thanks!

 

[jasonb75]

I cant test a formula on a screen capture and don't have time to retype your data to test this, the theory should be somewhere close but it will probably need some adjustments to work perfectly.

=SUMPRODUCT(SUMIFS($C$11:$C$20,$A$11:$A$20,B$3,$B$11:$B$20,IF(SUBTOTAL(3,OFFSET($A$4,ROW($A$4:$A$8)-ROW($A$4),0,1,1)),$A$4:$A$8,"")))

For posting samples it would be preferable if you used XL2BB so that we can copy and paste the data into excel for testing suggestions.

 

[junbuggle]

Oh wow... just wow. It works!

Thank you so much! I really do appreciate it. And I know that this isn't the first time you've been kind enough to respond to my posts. So thank you again I'm so grateful.

I would never have thought to use SUMIFS. So I was googling completely on the wrong track. So another reason I'm thankful for you responding.

And thanks for the suggestion about XL2BB. I'm on a work laptop so can't install add-ins, but it might be easier for me to post from my personal laptop in future.


Gratefully yours,

Gav

 

[junbuggle]

Hi @jasonb75

I was hoping to ask one further followup question...

I've copied the table into XL2BB and posted here (I didn't realise it was so easy!).

Do you know if there is a way to only pick up distinct values from Table A?

So in this example, (Bob and George) in March should be 6. But because George is counted twice, it's giving me 11. I would like to ignore the duplicate values in Table A.

I tried using UNIQUE to wrap the range in the IF statement (value if true), but that didn't work...


Thanks

Book1
	A	B	C
1	Table A
2		11	12
3		March	April
4	Bob
6	George
8	George
9
10	Table B
11	March	Bob	1
12	March	George	5
13	March	Hilda	7
14	March	Jenny	2
15	March	John	4
16	April	Bob	4
17	April	George	4
18	April	Hilda	5
19	April	Jenny	2
20	April	John	7
Sheet1

Cell Formulas
Range	Formula
B2:C2	B2	=SUMPRODUCT(SUMIFS($C$11:$C$20,$A$11:$A$20,B$3,$B$11:$B$20,IF(SUBTOTAL(3,OFFSET($A$4,ROW($A$4:$A$8)-ROW($A$4),0,1,1)),$A$4:$A$8,"")))

 

[jasonb75]

I tried using UNIQUE to wrap the range in the IF statement (value if true), but that didn't work...

You would need to wrap the entire IF, not just the value if true range. By only wrapping the value if true you are creating arrays that are of unequal size which will generate an error in that part of the formula.

=SUMPRODUCT(SUMIFS($C$11:$C$20,$A$11:$A$20,B$3,$B$11:$B$20,UNIQUE(IF(SUBTOTAL(3,OFFSET($A$4,ROW($A$4:$A$8)-ROW($A$4),0,1,1)),$A$4:$A$8,""))))

 

[junbuggle]

Ah okay, that makes sense! I'm indebted to you once again. Thanks @jasonb75 !