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