Sum & Vlookup Formula

[Sundance_Kid]

Hi,
I want to look up a value in another table and sum up the total across multiple columns (60 in total).

Is there a more efficient formula than having to individually enter each column number in the {2,3,4,5,6 etc} part of the formula?

Currently my formula looks like
Vlookup(C1, 'Data Sheet'!B:BK,{2,3,4,5,6,7} except I need to enter each number after 7 all the way up to 60.

Thanks

 

[Eric W]

Try:

Book2
	A	B	C
1		Value:	A
2		Sum:	354
Sheet4

Cell Formulas
Range	Formula
C2	C2	=SUM(FILTER('Data Sheet'!C2:BK1000,'Data Sheet'!B2:B1000=C1))

You can use whole column references here, but it will make it less efficient, so unless you really do have a million rows of data, it's better to use an upper bound on the row.

 

[Sundance_Kid]

Thank you, that worked.

What is the purpose of the FILTER element of the formula?

 

[FormR]

Hi, here's another option where you can be a less concerned about a performance hit when using full column references.

=SUM(INDEX('Data Sheet'!C:BK,MATCH(C1,'Data Sheet'!B:B,0),0))

 

[Eric W]

Thank you, that worked.

What is the purpose of the FILTER element of the formula?

The FILTER just removes all rows that don't start with your desired value (C1). Then the SUM just adds up the remaining row.