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


See More: SUMIF or VLOOKUP? a range of cells

Report •


#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


Ask Question