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 C ValueName (formula) Sheet 2 has my data on, in the format:- A B C D E ValueName 2 4 2 2 2 I 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

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?

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