Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I use LOOKUP to return a range of values, then SUM values?
Any thoughts? I have spreadsheet that has this years dates running down
column A. I then have each day's corresponding production figure running down column B. I want to assign a function to a cell that will result in the months cumulative production figure. (I realize I can manually select the desired Column B cells, but this is not the solution I am looking for) The formula I have come up with so far is: =SUM(LOOKUP(TEXT(TODAY(),"mmm"),TEXT(A1:A365,"mmm" ),B1:B365)) For some reason the result is 0. I used the formula auditing tool and came up with nothing. Any thoughts? Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I use LOOKUP to return a range of values, then SUM values?
Two questions: 1- why not sum in the talley cell with a simple function? 2- are you talleying the month or the year? it is not clear in your decription, each month then you total the year? -- userofexcel ------------------------------------------------------------------------ userofexcel's Profile: http://www.excelforum.com/member.php...o&userid=37040 View this thread: http://www.excelforum.com/showthread...hreadid=568176 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I use LOOKUP to return a range of values, then SUM values?
First, you don't need the Lookup, and second, you want Sumif, not Sum.
However, you're trying to sum based on two selections (greater than the 1st of the month and less than today). To get around this, you have two choices: 1. Add a helper column, say C, with the month (=text(a2,"mmm")). Then you can use: =sumif(C:C,text(today(),"mmm"),B:B) 2. Use Sumproduct, as in: =SUMPRODUCT(A1:A365=DATE(YEAR(TODAY()),MONTH(TODA Y()),1),A1:A365<=TODAY(),B1:B365) -- Regards, Fred "irvine79" wrote in message ... Any thoughts? I have spreadsheet that has this years dates running down column A. I then have each day's corresponding production figure running down column B. I want to assign a function to a cell that will result in the months cumulative production figure. (I realize I can manually select the desired Column B cells, but this is not the solution I am looking for) The formula I have come up with so far is: =SUM(LOOKUP(TEXT(TODAY(),"mmm"),TEXT(A1:A365,"mmm" ),B1:B365)) For some reason the result is 0. I used the formula auditing tool and came up with nothing. Any thoughts? Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I use LOOKUP to return a range of values, then SUM values?
Hi
=SUMIF(A1:A365,"=" & TEXT(DATE(2006,n,1),B1:B135) - SUMIF(A1:A365,"<=" & TEXT(DATE(2006,n+1,0),B1:B135) , or =SUMPRODUCT(--(MONTH(A1:A365)=n),B1:B135) , where n is the number of month. -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "irvine79" wrote in message ... Any thoughts? I have spreadsheet that has this years dates running down column A. I then have each day's corresponding production figure running down column B. I want to assign a function to a cell that will result in the months cumulative production figure. (I realize I can manually select the desired Column B cells, but this is not the solution I am looking for) The formula I have come up with so far is: =SUM(LOOKUP(TEXT(TODAY(),"mmm"),TEXT(A1:A365,"mmm" ),B1:B365)) For some reason the result is 0. I used the formula auditing tool and came up with nothing. Any thoughts? Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I use LOOKUP to return a range of values, then SUM valu
Fred: You're the man. Thanks for the help. It definitely got me headed in the
right direction. Any thoughts on how I'd average this months daily production totals from the 1st until any given day of the month using the same sumif logic? thanks "Fred Smith" wrote: First, you don't need the Lookup, and second, you want Sumif, not Sum. However, you're trying to sum based on two selections (greater than the 1st of the month and less than today). To get around this, you have two choices: 1. Add a helper column, say C, with the month (=text(a2,"mmm")). Then you can use: =sumif(C:C,text(today(),"mmm"),B:B) 2. Use Sumproduct, as in: =SUMPRODUCT(A1:A365=DATE(YEAR(TODAY()),MONTH(TODA Y()),1),A1:A365<=TODAY(),B1:B365) -- Regards, Fred "irvine79" wrote in message ... Any thoughts? I have spreadsheet that has this years dates running down column A. I then have each day's corresponding production figure running down column B. I want to assign a function to a cell that will result in the months cumulative production figure. (I realize I can manually select the desired Column B cells, but this is not the solution I am looking for) The formula I have come up with so far is: =SUM(LOOKUP(TEXT(TODAY(),"mmm"),TEXT(A1:A365,"mmm" ),B1:B365)) For some reason the result is 0. I used the formula auditing tool and came up with nothing. Any thoughts? Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I use LOOKUP to return a range of values, then SUM valu
Sure. Just divide by the day of the month, as in:
=sumif(..)/day(today()) Of, if you wanted to specify the day, say in c1, your average daily production from the 1st to c1 of the month is: =SUMPRODUCT(A1:A365=DATE(YEAR(TODAY()),MONTH(TODA Y()),1),A1:A365<=DATE(YEAR(TODAY()),MONTH(TODAY()) ,C1),B1:B365)/C1 -- Regards, Fred "irvine79" wrote in message ... Fred: You're the man. Thanks for the help. It definitely got me headed in the right direction. Any thoughts on how I'd average this months daily production totals from the 1st until any given day of the month using the same sumif logic? thanks "Fred Smith" wrote: First, you don't need the Lookup, and second, you want Sumif, not Sum. However, you're trying to sum based on two selections (greater than the 1st of the month and less than today). To get around this, you have two choices: 1. Add a helper column, say C, with the month (=text(a2,"mmm")). Then you can use: =sumif(C:C,text(today(),"mmm"),B:B) 2. Use Sumproduct, as in: =SUMPRODUCT(A1:A365=DATE(YEAR(TODAY()),MONTH(TODA Y()),1),A1:A365<=TODAY(),B1:B365) -- Regards, Fred "irvine79" wrote in message ... Any thoughts? I have spreadsheet that has this years dates running down column A. I then have each day's corresponding production figure running down column B. I want to assign a function to a cell that will result in the months cumulative production figure. (I realize I can manually select the desired Column B cells, but this is not the solution I am looking for) The formula I have come up with so far is: =SUM(LOOKUP(TEXT(TODAY(),"mmm"),TEXT(A1:A365,"mmm" ),B1:B365)) For some reason the result is 0. I used the formula auditing tool and came up with nothing. Any thoughts? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup function gives wrong values occasionally | Excel Discussion (Misc queries) | |||
How do I use Lookup to return a row of values eg. 6 cells | Excel Worksheet Functions | |||
how do i return a range of values | Excel Discussion (Misc queries) | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
Return range of values on an "IF" statement | Excel Worksheet Functions |