sumif with a lookup formula
 

I need a forumla that will work like a sumif but I need to do a lookup to
determine the column that is summed. In the example worksheet below, on
sheet 1 cell B2, i need a formula that will sum from sheet 2 Column C for all
the 3100 references. The dates on Sheet 2 can change so the Column
refernence could change. I tried using a vlookup, with a match/indirect
forumla to return the column number, but with multiple 3100's, that formula
doesn't sum the numbers.


Sheet1
Column A ColumnB Column C Column D
Labor cat 07/01/09 08/01/09 09/01/09
3100
4200
5200

Sheet 2
Column A ColumnB Column C Column D
Labor cat 06/01/09 07/01/09 09/01/09
3100 25 72
30
4200 15 20
80
5200 50 60
3
3100 23 52
5

Any help is greatly appreciated, and if i can clarify anything just let me
know.

Thanks!

sumif with a lookup formula
 

Try this...

=SUMIF(Sheet2!$A$2:$A$100,$A2,INDEX(Sheet2!$B$2:$D $100,MATCH(B$1,Sheet2!$B$1:$D$1,0)))

--
Biff
Microsoft Excel MVP


"Chas" wrote in message
...
I need a forumla that will work like a sumif but I need to do a lookup to
determine the column that is summed. In the example worksheet below, on
sheet 1 cell B2, i need a formula that will sum from sheet 2 Column C for
all
the 3100 references. The dates on Sheet 2 can change so the Column
refernence could change. I tried using a vlookup, with a match/indirect
forumla to return the column number, but with multiple 3100's, that
formula
doesn't sum the numbers.


Sheet1
Column A ColumnB Column C Column D
Labor cat 07/01/09 08/01/09 09/01/09
3100
4200
5200

Sheet 2
Column A ColumnB Column C Column D
Labor cat 06/01/09 07/01/09 09/01/09
3100 25 72
30
4200 15 20
80
5200 50 60
3
3100 23 52
5

Any help is greatly appreciated, and if i can clarify anything just let me
know.

Thanks!

sumif with a lookup formula
 

Hi,

Try something like this

=SUMPRODUCT((Sheet2!$A$2:$A$9=$A2)*(Sheet2!$B$1:$D $1=B$1)*Sheet2!$B$2:$D$4)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Chas" wrote:

I need a forumla that will work like a sumif but I need to do a lookup to
determine the column that is summed. In the example worksheet below, on
sheet 1 cell B2, i need a formula that will sum from sheet 2 Column C for all
the 3100 references. The dates on Sheet 2 can change so the Column
refernence could change. I tried using a vlookup, with a match/indirect
forumla to return the column number, but with multiple 3100's, that formula
doesn't sum the numbers.


Sheet1
Column A ColumnB Column C Column D
Labor cat 07/01/09 08/01/09 09/01/09
3100
4200
5200

Sheet 2
Column A ColumnB Column C Column D
Labor cat 06/01/09 07/01/09 09/01/09
3100 25 72
30
4200 15 20
80
5200 50 60
3
3100 23 52
5

Any help is greatly appreciated, and if i can clarify anything just let me
know.

Thanks!

sumif with a lookup formula
 

Perfect, thanks so much for your help!

"T. Valko" wrote:

Try this...

=SUMIF(Sheet2!$A$2:$A$100,$A2,INDEX(Sheet2!$B$2:$D $100,MATCH(B$1,Sheet2!$B$1:$D$1,0)))

--
Biff
Microsoft Excel MVP


"Chas" wrote in message
...
I need a forumla that will work like a sumif but I need to do a lookup to
determine the column that is summed. In the example worksheet below, on
sheet 1 cell B2, i need a formula that will sum from sheet 2 Column C for
all
the 3100 references. The dates on Sheet 2 can change so the Column
refernence could change. I tried using a vlookup, with a match/indirect
forumla to return the column number, but with multiple 3100's, that
formula
doesn't sum the numbers.


Sheet1
Column A ColumnB Column C Column D
Labor cat 07/01/09 08/01/09 09/01/09
3100
4200
5200

Sheet 2
Column A ColumnB Column C Column D
Labor cat 06/01/09 07/01/09 09/01/09
3100 25 72
30
4200 15 20
80
5200 50 60
3
3100 23 52
5

Any help is greatly appreciated, and if i can clarify anything just let me
know.

Thanks!

sumif with a lookup formula
 

=SUMIF(Sheet2!$A$2:$A$100,$A2,INDEX(Sheet2!$B$2:$ D$100,MATCH(B$1,Sheet2!$B$1:$D$1,0)))

Actually, there's a problem with that formula.

Thanks to Barry for catching it. Try it like this:

=SUMIF(Sheet2!$A$2:$A$100,$A2,INDEX(Sheet2!$B$2:$D $100,0,MATCH(B$1,Sheet2!$B$1:$D$1,0)))

--
Biff
Microsoft Excel MVP


"Chas" wrote in message
...
Perfect, thanks so much for your help!

"T. Valko" wrote:

Try this...

=SUMIF(Sheet2!$A$2:$A$100,$A2,INDEX(Sheet2!$B$2:$D $100,MATCH(B$1,Sheet2!$B$1:$D$1,0)))

--
Biff
Microsoft Excel MVP


"Chas" wrote in message
...
I need a forumla that will work like a sumif but I need to do a lookup
to
determine the column that is summed. In the example worksheet below,
on
sheet 1 cell B2, i need a formula that will sum from sheet 2 Column C
for
all
the 3100 references. The dates on Sheet 2 can change so the Column
refernence could change. I tried using a vlookup, with a
match/indirect
forumla to return the column number, but with multiple 3100's, that
formula
doesn't sum the numbers.


Sheet1
Column A ColumnB Column C Column D
Labor cat 07/01/09 08/01/09
09/01/09
3100
4200
5200

Sheet 2
Column A ColumnB Column C Column D
Labor cat 06/01/09 07/01/09
09/01/09
3100 25 72
30
4200 15 20
80
5200 50 60
3
3100 23 52
5

Any help is greatly appreciated, and if i can clarify anything just let
me
know.

Thanks!