SUMIFS Using VLOOKUP Range

[MPierce486]

I am trying to SUMIFS using multiple criteria, but one of the criteria is dependent on another table. See below.

I am trying to sum up all amounts with a "111" account number AND where the "Code" is equal to "Equity". The first part is easy by using =SUMIF(A3:A8,111,C3:C8). However, I'm unable to a VLOOKUP for the next part since I cannot select a range as the lookup_value. Using =SUMIF(VLOOKUP(B3:B8,E3:F7,2),Equity,C3:C8) does not work.

I have tried using SUMPRODUCT or SUMIF along with INDEX & MATCH, but no success yet. Any help is appreciated. Thank you!

 

[JamesCanale]

MrExcelPlayground6.xlsx
	A	B	C	D	E	F
2	Account	Code	Amount		code	class
3	111	423	154546		228	Equity
4	111	375	61856		271	Equity
5	111	271	3318		375	Fixed Income
6	222	423	155962		423	Cash
7	222	228	1863		576	Equity
8	222	576	15632
9
10	111	Equity	3318	Office 365
11
12	111	Equity	3318	Older than 365
Sheet31

Cell Formulas
Range	Formula
C10	C10	=SUM(FILTER(C3:C8,(A3:A8=A10)*(VLOOKUP(B3:B8,E3:F7,2,FALSE)=B10)))
C12	C12	=SUMPRODUCT(C3:C8,--(A3:A8=A12),--(VLOOKUP(B3:B8,E3:F7,2,FALSE)=B12))

 

[MPierce486]

MrExcelPlayground6.xlsx
	A	B	C	D	E	F
2	Account	Code	Amount		code	class
3	111	423	154546		228	Equity
4	111	375	61856		271	Equity
5	111	271	3318		375	Fixed Income
6	222	423	155962		423	Cash
7	222	228	1863		576	Equity
8	222	576	15632
9
10	111	Equity	3318	Office 365
11
12	111	Equity	3318	Older than 365
Sheet31

Cell Formulas
Range	Formula
C10	C10	=SUM(FILTER(C3:C8,(A3:A8=A10)*(VLOOKUP(B3:B8,E3:F7,2,FALSE)=B10)))
C12	C12	=SUMPRODUCT(C3:C8,--(A3:A8=A12),--(VLOOKUP(B3:B8,E3:F7,2,FALSE)=B12))

I get a #VALUE error in the VLOOKUP due to the B3:B8 reference.

 

[JamesCanale]

What version are you running?

 

[MPierce486]

What version are you running?

The bottom one referencing cell C12. I tried the top, but don't have the FILTER function.

 

[JamesCanale]

Does this work?

MrExcelPlayground6.xlsx
	A	B	C	D	E	F
2	Account	Code	Amount		code	class
3	111	423	154546		228	Equity
4	111	375	61856		271	Equity
5	111	271	3318		375	Fixed Income
6	222	423	155962		423	Cash
7	222	228	1863		576	Equity
8	222	576	15632
9
10	111	Equity	3318	Office 365
11
12	111	Equity	3318	Older than 365
13
14	111	Equity	3318	Another Thing
Sheet31

Cell Formulas
Range	Formula
C10	C10	=SUM(FILTER(C3:C8,(A3:A8=A10)*(VLOOKUP(B3:B8,E3:F7,2,FALSE)=B10)))
C12	C12	=SUMPRODUCT(C3:C8,--(A3:A8=A12),--(VLOOKUP(B3:B8,E3:F7,2,FALSE)=B12))
C14	C14	=SUMPRODUCT(C3:C8,--(A3:A8=A12),--(INDEX(F3:F7,MATCH(B3:B8,E3:E7,0))=B14))

 

[MPierce486]

Does this work?

MrExcelPlayground6.xlsx
	A	B	C	D	E	F
2	Account	Code	Amount		code	class
3	111	423	154546		228	Equity
4	111	375	61856		271	Equity
5	111	271	3318		375	Fixed Income
6	222	423	155962		423	Cash
7	222	228	1863		576	Equity
8	222	576	15632
9
10	111	Equity	3318	Office 365
11
12	111	Equity	3318	Older than 365
13
14	111	Equity	3318	Another Thing
Sheet31

Cell Formulas
Range	Formula
C10	C10	=SUM(FILTER(C3:C8,(A3:A8=A10)*(VLOOKUP(B3:B8,E3:F7,2,FALSE)=B10)))
C12	C12	=SUMPRODUCT(C3:C8,--(A3:A8=A12),--(VLOOKUP(B3:B8,E3:F7,2,FALSE)=B12))
C14	C14	=SUMPRODUCT(C3:C8,--(A3:A8=A12),--(INDEX(F3:F7,MATCH(B3:B8,E3:E7,0))=B14))

The reference in MATCH returns #VALUE. Aren't only supposed to have a single "lookup_value" in MATCH and VLOOKUP?

 

[JamesCanale]

You can do a helper column. Put the vlookup in column D and use that in the sumproduct. I guess you are running an old excel.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[MPierce486]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Whoops, it's 2016. Just updated the account details.