Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need a Lookup/Sumif Combo Formula | Excel Worksheet Functions | |||
How do i combine a lookup and a sumif formula? | Excel Discussion (Misc queries) | |||
Sumif/lookup ? Array | Excel Discussion (Misc queries) | |||
sumif? lookup? | Excel Worksheet Functions | |||
Help with SUMIF, INDEX, LOOKUP Please !! | Excel Worksheet Functions |