# SUMIF or VLOOKUP? a range of cells

November 21, 2007 at 09:16:30
Specs: XP, Athlon XP2100
 Hi,I'm trying to perform a VLOOKUP or SUMIF on a range of data in another sheet.Eg Sheet 1 is where i the formula is. Col CValueName (formula)Sheet 2 has my data on, in the format:- A B C D EValueName 2 4 2 2 2I can obviously lookup against a single Column, eg B and return the value "4" with the following formula. =SUMIF('sheet2'!\$C:\$C,Valuename\$C:\$C,'Sheet2'!\$B:\$B)However, what i want to do is lookup the sum of A-E to return the value "12"I'm sure there is a simple way to do it but cant think of it at the moment, so can anyone help?Thanks

See More: SUMIF or VLOOKUP? a range of cells

#1
November 22, 2007 at 22:42:37
 Maybe it's just because I'm groggy from a big Thanksgiving meal, but I gotta admit that I have no clue what you are asking for.Let's start with this:"Eg Sheet 1 is where i the formula is."What does "where i the formula is" mean?Next:Col C"ValueName (formula)"What does this mean? I don't know of any function called "ValueName"Let me take a guess: Are you trying to search a range of values in A - E and find a set of values from a single row that sums to 12?

Report •

#2
December 5, 2007 at 11:46:51
 you can use sumif(sheet2!A:A,C1,Sheet2!B:B)+sumif(sheet2!A:A,c1,sheet2!C:C)+sumif(sheet2!A:A,c1,sheet2!D:D) etc etc.But it ain't ideal.Another option is using INDIRECT - if each field only appears once in the data sheet, you can just use=sum(indirect("sheet2!B"&match(C1,sheet2!A:A,0)&":E"&match(c1,sheet2!A:A,0))failing that, I'm sure there is something you can do with sumproduct, but I can't remember the right syntax for that

Report •

Related Solutions