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 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

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

Ask Your Question

Weekly Poll

Do you believe Microsoft when it says it views the "Desktop" as the core of Windows?

Discuss in The Lounge

Poll History