Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing HLookup Values
I have a chart of numbers pertaining to progress of a project by month. The
end of month dates are in a row across the top, the progress % are in corresponding rows and columns below: 31-mar-06 30-apr-06 31-may-06 30-jun-06 5.5 3.4 1.2 2.6 I'm trying to do a SUM of HLOOKUP values to get a total progress for a given month. For example, I need to see the total progress for end of May from end of March: SUM(HLOOKUP(5.5) to HLOOKUP(1.2))=5.5+3.4+1.2 in a cell at the end of the row. How can I do this? Do I need to use another function? Thanx............TomCat |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing HLookup Values
Hi!
One way: You need 2 cells to hold the span that you're interested in. Assume the 12 EOM dates are in the range A1:L1 N1 = header = From O1 = header = To N2 = Mar O2 = May =SUMIF(A1:L1,"="&DATEVALUE(N2&" "&2006),A2:L2)-SUMIF(A1:L1,""&DATEVALUE(O2&" "&2006)+32,A2:L2) Format the formula cell as GENERAL Biff "TomCat" wrote in message ... I have a chart of numbers pertaining to progress of a project by month. The end of month dates are in a row across the top, the progress % are in corresponding rows and columns below: 31-mar-06 30-apr-06 31-may-06 30-jun-06 5.5 3.4 1.2 2.6 I'm trying to do a SUM of HLOOKUP values to get a total progress for a given month. For example, I need to see the total progress for end of May from end of March: SUM(HLOOKUP(5.5) to HLOOKUP(1.2))=5.5+3.4+1.2 in a cell at the end of the row. How can I do this? Do I need to use another function? Thanx............TomCat |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing HLookup Values
I forgot something!
Assume the 12 EOM dates are in the range A1:L1 AND the numbers are in the range A2:L2. Biff "Biff" wrote in message ... Hi! One way: You need 2 cells to hold the span that you're interested in. Assume the 12 EOM dates are in the range A1:L1 N1 = header = From O1 = header = To N2 = Mar O2 = May =SUMIF(A1:L1,"="&DATEVALUE(N2&" "&2006),A2:L2)-SUMIF(A1:L1,""&DATEVALUE(O2&" "&2006)+32,A2:L2) Format the formula cell as GENERAL Biff "TomCat" wrote in message ... I have a chart of numbers pertaining to progress of a project by month. The end of month dates are in a row across the top, the progress % are in corresponding rows and columns below: 31-mar-06 30-apr-06 31-may-06 30-jun-06 5.5 3.4 1.2 2.6 I'm trying to do a SUM of HLOOKUP values to get a total progress for a given month. For example, I need to see the total progress for end of May from end of March: SUM(HLOOKUP(5.5) to HLOOKUP(1.2))=5.5+3.4+1.2 in a cell at the end of the row. How can I do this? Do I need to use another function? Thanx............TomCat |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing HLookup Values
I also forgot to mention that this project spans over several years and has
133 rows below the header line, which contains the dates. Dates 31-mar-06 30-apr-06 31-may-06 30-jun-06 out to end dec 09 Task 1 5.5 3.4 1.2 2.6 Task 2 2.1 5.5 4.3 2.5 etc....down Any way to make this easier? Thanks............TC "Biff" wrote: I forgot something! Assume the 12 EOM dates are in the range A1:L1 AND the numbers are in the range A2:L2. Biff "Biff" wrote in message ... Hi! One way: You need 2 cells to hold the span that you're interested in. Assume the 12 EOM dates are in the range A1:L1 N1 = header = From O1 = header = To N2 = Mar O2 = May =SUMIF(A1:L1,"="&DATEVALUE(N2&" "&2006),A2:L2)-SUMIF(A1:L1,""&DATEVALUE(O2&" "&2006)+32,A2:L2) Format the formula cell as GENERAL Biff "TomCat" wrote in message ... I have a chart of numbers pertaining to progress of a project by month. The end of month dates are in a row across the top, the progress % are in corresponding rows and columns below: 31-mar-06 30-apr-06 31-may-06 30-jun-06 5.5 3.4 1.2 2.6 I'm trying to do a SUM of HLOOKUP values to get a total progress for a given month. For example, I need to see the total progress for end of May from end of March: SUM(HLOOKUP(5.5) to HLOOKUP(1.2))=5.5+3.4+1.2 in a cell at the end of the row. How can I do this? Do I need to use another function? Thanx............TomCat |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing HLookup Values
Sorry, Biff. Didn't work.
"Biff" wrote: I forgot something! Assume the 12 EOM dates are in the range A1:L1 AND the numbers are in the range A2:L2. Biff "Biff" wrote in message ... Hi! One way: You need 2 cells to hold the span that you're interested in. Assume the 12 EOM dates are in the range A1:L1 N1 = header = From O1 = header = To N2 = Mar O2 = May =SUMIF(A1:L1,"="&DATEVALUE(N2&" "&2006),A2:L2)-SUMIF(A1:L1,""&DATEVALUE(O2&" "&2006)+32,A2:L2) Format the formula cell as GENERAL Biff "TomCat" wrote in message ... I have a chart of numbers pertaining to progress of a project by month. The end of month dates are in a row across the top, the progress % are in corresponding rows and columns below: 31-mar-06 30-apr-06 31-may-06 30-jun-06 5.5 3.4 1.2 2.6 I'm trying to do a SUM of HLOOKUP values to get a total progress for a given month. For example, I need to see the total progress for end of May from end of March: SUM(HLOOKUP(5.5) to HLOOKUP(1.2))=5.5+3.4+1.2 in a cell at the end of the row. How can I do this? Do I need to use another function? Thanx............TomCat |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing HLookup Values
Biff,
This doesn't work. I've tried it every which way but loose and can't get it to adapt. My dates are in Row 47 from L47 to CQ47, including quarters. The data I'm trying to look up and sum is located 7 rows below in Row 53. I need to sum the numbers between L53 (31-Dec-04) and BO53 (31-Mar-08) and divide the total by 2. I will be entering the date range in CU46 (31-Dec-04) and CV56 (31-Mar-08). This range is variable so the formula must read the start date in CU46 and the end date in CV56. I will be doing this function for all 133 rows below Row 47. In short, look up the start date and the end date in Row 47. Go down vertically 7 rows and find the numbers associated with the start and end date respectively. Sum those numbers and divide by 2. Thanks again......Tommy "Biff" wrote: I forgot something! Assume the 12 EOM dates are in the range A1:L1 AND the numbers are in the range A2:L2. Biff "Biff" wrote in message ... Hi! One way: You need 2 cells to hold the span that you're interested in. Assume the 12 EOM dates are in the range A1:L1 N1 = header = From O1 = header = To N2 = Mar O2 = May =SUMIF(A1:L1,"="&DATEVALUE(N2&" "&2006),A2:L2)-SUMIF(A1:L1,""&DATEVALUE(O2&" "&2006)+32,A2:L2) Format the formula cell as GENERAL Biff "TomCat" wrote in message ... I have a chart of numbers pertaining to progress of a project by month. The end of month dates are in a row across the top, the progress % are in corresponding rows and columns below: 31-mar-06 30-apr-06 31-may-06 30-jun-06 5.5 3.4 1.2 2.6 I'm trying to do a SUM of HLOOKUP values to get a total progress for a given month. For example, I need to see the total progress for end of May from end of March: SUM(HLOOKUP(5.5) to HLOOKUP(1.2))=5.5+3.4+1.2 in a cell at the end of the row. How can I do this? Do I need to use another function? Thanx............TomCat |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing HLookup Values
Perhaps give this a try as well ..
With EOM real dates as col headers assumed in B1:AU1, data in row2 down Let's assume EOM date inputs for the desired start-end period will be specified within AW1:AX1 (say), for eg: AW contains: 31-Mar-06, AX1 contains: 31-May-06 Then placed in AV2: =IF(OR($AW$1="",$AX$1=""),"",SUM(OFFSET(A2,,MATCH( $AW$1,$B$1:$AU$1,0),,MATCH($AX$1,$B$1:$AU$1,0)-MATCH($AW$1,$B$1:$AU$1,0)+1))) Copy AV2 down as far as required Col AV should return the desired results .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "TomCat" wrote: I also forgot to mention that this project spans over several years and has 133 rows below the header line, which contains the dates. Dates 31-mar-06 30-apr-06 31-may-06 30-jun-06 out to end dec 09 Task 1 5.5 3.4 1.2 2.6 Task 2 2.1 5.5 4.3 2.5 etc....down |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing HLookup Values
Re my response in the other branch ..
Try this adaptation in say, CT53: =IF(OR($CU$46="",$CV$46=""),"",SUM(OFFSET(K53,,MAT CH($CU$46,$L$47:$CQ$47,0),,MATCH($CV$46,$L$47:$CQ$ 47,0)-MATCH($CU$46,$L$47:$CQ$47,0)+1))/2) Copy CT53 down -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "TomCat" wrote: .... My dates are in Row 47 from L47 to CQ47, including quarters. The data I'm trying to look up and sum is located 7 rows below in Row 53. I need to sum the numbers between L53 (31-Dec-04) and BO53 (31-Mar-08) and divide the total by 2. I will be entering the date range in CU46 (31-Dec-04) and CV56 (31-Mar-08). This range is variable so the formula must read the start date in CU46 and the end date in CV56. I will be doing this function for all 133 rows below Row 47. In short, look up the start date and the end date in Row 47. Go down vertically 7 rows and find the numbers associated with the start and end date respectively. Sum those numbers and divide by 2. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing HLookup Values
Try this:
=(SUMIF(L47:CQ47,"="&CU46,L53:CQ53)-SUMIF(L47:CQ47,""&CV56,L53:CQ53))/2 Biff "TomCat" wrote in message ... Biff, This doesn't work. I've tried it every which way but loose and can't get it to adapt. My dates are in Row 47 from L47 to CQ47, including quarters. The data I'm trying to look up and sum is located 7 rows below in Row 53. I need to sum the numbers between L53 (31-Dec-04) and BO53 (31-Mar-08) and divide the total by 2. I will be entering the date range in CU46 (31-Dec-04) and CV56 (31-Mar-08). This range is variable so the formula must read the start date in CU46 and the end date in CV56. I will be doing this function for all 133 rows below Row 47. In short, look up the start date and the end date in Row 47. Go down vertically 7 rows and find the numbers associated with the start and end date respectively. Sum those numbers and divide by 2. Thanks again......Tommy "Biff" wrote: I forgot something! Assume the 12 EOM dates are in the range A1:L1 AND the numbers are in the range A2:L2. Biff "Biff" wrote in message ... Hi! One way: You need 2 cells to hold the span that you're interested in. Assume the 12 EOM dates are in the range A1:L1 N1 = header = From O1 = header = To N2 = Mar O2 = May =SUMIF(A1:L1,"="&DATEVALUE(N2&" "&2006),A2:L2)-SUMIF(A1:L1,""&DATEVALUE(O2&" "&2006)+32,A2:L2) Format the formula cell as GENERAL Biff "TomCat" wrote in message ... I have a chart of numbers pertaining to progress of a project by month. The end of month dates are in a row across the top, the progress % are in corresponding rows and columns below: 31-mar-06 30-apr-06 31-may-06 30-jun-06 5.5 3.4 1.2 2.6 I'm trying to do a SUM of HLOOKUP values to get a total progress for a given month. For example, I need to see the total progress for end of May from end of March: SUM(HLOOKUP(5.5) to HLOOKUP(1.2))=5.5+3.4+1.2 in a cell at the end of the row. How can I do this? Do I need to use another function? Thanx............TomCat |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing HLookup Values
Max and Biff, thanks for your help. Biff, I could never get yours to work.
Max, yours worked great except in a couple of lines where it's off by .53. That will be my mission for the morning. Max: I need an explanation of what you did. Can you walk me thru the formula? I saw some double commas there? I'm not familiar with that syntax. If you could be so kind as to step me thru it! Thanks VERY MUCH, Y'ALL!...........TomCat! "Max" wrote: Re my response in the other branch .. Try this adaptation in say, CT53: =IF(OR($CU$46="",$CV$46=""),"",SUM(OFFSET(K53,,MAT CH($CU$46,$L$47:$CQ$47,0),,MATCH($CV$46,$L$47:$CQ$ 47,0)-MATCH($CU$46,$L$47:$CQ$47,0)+1))/2) Copy CT53 down -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "TomCat" wrote: ... My dates are in Row 47 from L47 to CQ47, including quarters. The data I'm trying to look up and sum is located 7 rows below in Row 53. I need to sum the numbers between L53 (31-Dec-04) and BO53 (31-Mar-08) and divide the total by 2. I will be entering the date range in CU46 (31-Dec-04) and CV56 (31-Mar-08). This range is variable so the formula must read the start date in CU46 and the end date in CV56. I will be doing this function for all 133 rows below Row 47. In short, look up the start date and the end date in Row 47. Go down vertically 7 rows and find the numbers associated with the start and end date respectively. Sum those numbers and divide by 2. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing HLookup Values
"TomCat" wrote:
Max and Biff, thanks for your help. Biff, I could never get yours to work. Max, yours worked great except in a couple of lines where it's off by .53. That will be my mission for the morning. Maybe re-check the values within the lines. If it works, it should work ok for all lines as we copy down from CT53 Max: I need an explanation of what you did. Can you walk me thru the formula? I saw some double commas there? I'm not familiar with that syntax. If you could be so kind as to step me thru it! At the crux of it is the OFFSET expression within the SUM(OFFSET(...)) ie, in partially decomposed form, the expression: OFFSET(K53,, MATCH($CU$46,$L$47:$CQ$47,0),, MATCH($CV$46,$L$47:$CQ$47,0)-MATCH($CU$46,$L$47:$CQ$47,0)+1) From help, the syntax is: OFFSET(reference,rows,cols,height,width), so for the above reference point is K53 ie the row 53 in question rows param is zero (the optional zero is omitted here, with the rows param represented by the double commas next to K53, viz.: K53,,) cols param is returned by the expression: MATCH($CU$46,$L$47:$CQ$47,0) viz the position of the matched EOM startdate (CU46) within the EOM array $L$47:$CQ$47 height param is zero (the optional zero is omitted here, with the height param represented by the double commas next to the cols param, ie: MATCH($CU$46,$L$47:$CQ$47,0),, width param is returned by the expression: MATCH($CV$46,$L$47:$CQ$47,0)-MATCH($CU$46,$L$47:$CQ$47,0)+1 which is essentially the difference between the matched EOM enddate's (CV46) position within $L$47:$CQ$47 minus the matched startdate's (CU46) position within $L$47:$CQ$47 plus "1" (+1) as an arithmetic adjustment So the expression OFFSET(K53,...) will evaluate and return the range/array of values within L53:CQ53 starting with the col of the matched EOM startdate and ending exactly where the matched EOM enddate col lies And when the OFFSET(K53,...) is copied down, the reference increments relatively to OFFSET(K54,...), OFFSET(K55,...), and so on, hence returning correspondingly the required range of values in row 54, 55, and so on as we copy down -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing HLookup Values
Biff, I could never get yours to work.
Well, I can put together a sample file that demonstrates that it does work or, if you'd like to, you can send me a copy of your file and I'll put the formula in for you. If you're interested in doing either of these just let me know how to contact you. Biff "TomCat" wrote in message ... Max and Biff, thanks for your help. Biff, I could never get yours to work. Max, yours worked great except in a couple of lines where it's off by .53. That will be my mission for the morning. Max: I need an explanation of what you did. Can you walk me thru the formula? I saw some double commas there? I'm not familiar with that syntax. If you could be so kind as to step me thru it! Thanks VERY MUCH, Y'ALL!...........TomCat! "Max" wrote: Re my response in the other branch .. Try this adaptation in say, CT53: =IF(OR($CU$46="",$CV$46=""),"",SUM(OFFSET(K53,,MAT CH($CU$46,$L$47:$CQ$47,0),,MATCH($CV$46,$L$47:$CQ$ 47,0)-MATCH($CU$46,$L$47:$CQ$47,0)+1))/2) Copy CT53 down -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "TomCat" wrote: ... My dates are in Row 47 from L47 to CQ47, including quarters. The data I'm trying to look up and sum is located 7 rows below in Row 53. I need to sum the numbers between L53 (31-Dec-04) and BO53 (31-Mar-08) and divide the total by 2. I will be entering the date range in CU46 (31-Dec-04) and CV56 (31-Mar-08). This range is variable so the formula must read the start date in CU46 and the end date in CV56. I will be doing this function for all 133 rows below Row 47. In short, look up the start date and the end date in Row 47. Go down vertically 7 rows and find the numbers associated with the start and end date respectively. Sum those numbers and divide by 2. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing HLookup Values
Max's formula will work but it's like killing an ant with a sledgehammer
when all you needed to do was flick it with your finger! <g But if it works for you, great, that's what counts! Biff "Biff" wrote in message ... Biff, I could never get yours to work. Well, I can put together a sample file that demonstrates that it does work or, if you'd like to, you can send me a copy of your file and I'll put the formula in for you. If you're interested in doing either of these just let me know how to contact you. Biff "TomCat" wrote in message ... Max and Biff, thanks for your help. Biff, I could never get yours to work. Max, yours worked great except in a couple of lines where it's off by .53. That will be my mission for the morning. Max: I need an explanation of what you did. Can you walk me thru the formula? I saw some double commas there? I'm not familiar with that syntax. If you could be so kind as to step me thru it! Thanks VERY MUCH, Y'ALL!...........TomCat! "Max" wrote: Re my response in the other branch .. Try this adaptation in say, CT53: =IF(OR($CU$46="",$CV$46=""),"",SUM(OFFSET(K53,,MAT CH($CU$46,$L$47:$CQ$47,0),,MATCH($CV$46,$L$47:$CQ$ 47,0)-MATCH($CU$46,$L$47:$CQ$47,0)+1))/2) Copy CT53 down -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "TomCat" wrote: ... My dates are in Row 47 from L47 to CQ47, including quarters. The data I'm trying to look up and sum is located 7 rows below in Row 53. I need to sum the numbers between L53 (31-Dec-04) and BO53 (31-Mar-08) and divide the total by 2. I will be entering the date range in CU46 (31-Dec-04) and CV56 (31-Mar-08). This range is variable so the formula must read the start date in CU46 and the end date in CV56. I will be doing this function for all 133 rows below Row 47. In short, look up the start date and the end date in Row 47. Go down vertically 7 rows and find the numbers associated with the start and end date respectively. Sum those numbers and divide by 2. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing HLookup Values
"Biff" wrote:
Biff, I could never get yours to work. Well, I can put together a sample file that demonstrates that it does work .. C'mon, Biff <g. Don't hold back. Throw a link to your working sample here to benefit all readers within the newsgroup who may be interested. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing HLookup Values
"Biff" wrote:
Max's formula will work but it's like killing an ant with a sledgehammer when all you needed to do was flick it with your finger! <g But if it works for you, great, that's what counts Thanks for that! It's my style to always strive to deliver more than what is asked in the OP <bg This may include: Front error traps, absolute/mixed/relative cell references, warts and all, everything that's needed to get going on the OP's end. The resulting concoction is of course just one route to get to Rome, and it could almost always be refined further. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing HLookup Values
"Max" wrote...
"Biff" wrote: Biff, I could never get yours to work. Well, I can put together a sample file that demonstrates that it does work .. C'mon, Biff <g. Don't hold back. Throw a link to your working sample here to benefit all readers within the newsgroup who may be interested. I love putting together sample files! My dates are in Row 47 from L47 to CQ47, including quarters. The data I'm trying to look up and sum is located 7 rows below in Row 53. I need to sum the numbers between L53 (31-Dec-04) and BO53 (31-Mar-08) and divide the total by 2. I will be entering the date range in CU46 (31-Dec-04) and CV56 (31-Mar-08). This range is variable so the formula must read the start date in CU46 and the end date in CV56. Sample file: Sample_Sumif.xls 14.5KB http://cjoint.com/?hyc3RiK7dG Biff |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing HLookup Values
"Biff" wrote:
.. Sample file: Sample_Sumif.xls 14.5KB http://cjoint.com/?hyc3RiK7dG Thanks for that! Here's just a quick rendition of your sample with both suggestions thrown-in: http://cjoint.com/?hydHHzonve TomCat_1.xls I did dress up the cell refs for your shorter equation (it's still shorter, notwithstanding the additional "$" signs added <g) since the OP said: .. I will be doing this function for all 133 rows below Row 47. which implied he'd want the formula in ready-to-copy-down form Looking at your sample, I just realized I had earlier mistaken the EOM enddate's cell ref as CV46, when it should be CV56 (as posted by OP). Then again, perhaps there was a typo in the OP, maybe what was mistaken was correct <g. Cheers. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing non hidden values in a range | Excel Discussion (Misc queries) | |||
Summing values if conditions are true | Excel Worksheet Functions | |||
Summing values within a range | Excel Discussion (Misc queries) | |||
What is the best method for summing values where you want 2 value. | Excel Discussion (Misc queries) | |||
Summing Related Values | Excel Discussion (Misc queries) |