 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / March 2008MATCH and OFFSET with dynamic range - Excel Formula
Hello, I would like to do the following with excel Formula. these are the information Sheet 2 A M 01/12/2001 24 01/06/2002 23 01/12/2002 25 01/06/2003 52 01/12/2003 53 there are already 2 range define in sheet 2 rngDate, rgnValues How to obtain something like this with Excel formula and not VBA Sheet1 Jun Dec tot 2003 52 53 105 2002 23 25 48 2001 24 24 I tried to use Match and Offset (but I have a problem with the offset) Any help will be really apreciate :D Ina I noticed that all of the dates are either June or December. Assume on sheet1: Column headers B1:D1 = Jun, Dec, Tot Row headers A2:A4 = 2003, 2002, 2001 Enter this formula in B2: =SUMPRODUCT(--(MONTH(rngDate)=COLUMNS($B2:B2)*6),--(YEAR(rngDate)=$A2),rngValues) Copy across to C2 then down to row 4. Enter this formula in D2 and copy down to D4: =SUM(B2:C2)  SignatureBiff Microsoft Excel MVP > Hello, > [quoted text clipped - 25 lines] > > Ina > I noticed that all of the dates are either June or December. > [quoted text clipped - 52 lines] > > - Show quoted text - Hello Thank you It is working perfectly Only one question if I have monthly data ? can I adapt the formula and I can use dynamic Range ? thank you again :D Ina If you have monthly data and your column headers are like this: B1:M1 = Jan, Feb, Mar, Apr .... Dec Then change the formula to: =SUMPRODUCT(--(MONTH(rngDate)=COLUMNS($B2:B2)),--(YEAR(rngDate)=$A2),rngValues) SignatureBiff Microsoft Excel MVP On Mar 3, 9:02 am, "T. Valko" <biffinp...@comcast.net> wrote: > I noticed that all of the dates are either June or December. > [quoted text clipped - 52 lines] > > - Show quoted text - Hello Thank you It is working perfectly Only one question if I have monthly data ? can I adapt the formula and I can use dynamic Range ? thank you again :D Ina > If you have monthly data and your column headers are like this: > [quoted text clipped - 77 lines] > > - Show quoted text - Thank you so much :D It is working very well You're welcome. Thanks for the feedback! SignatureBiff Microsoft Excel MVP On Mar 3, 9:57 am, "T. Valko" <biffinp...@comcast.net> wrote: > If you have monthly data and your column headers are like this: > [quoted text clipped - 76 lines] > > - Show quoted text - Thank you so much :D It is working very well
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.