 |
 | 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 / August 2007month lookup
i have the following formula =IF(MONTH($B:$B)=MONTH(J1),+SUMIF($H:$H,"auto",$F:$F)+SUMIF($H: $H,"auto",$E:$E)) problem is i am getting results (sum) of all of column F and E I only want the amounts in F and E if the applicable row is the month of jan B E F H 01/01/07 200.00 55.00 auto 01/15/07 201.00 0.00 auto 02/01/07 100.00 25.00 auto 02/15/07 125.00 35.00 milk So for Jan - i should get 456.00 What am i missing here. Try it like this: =SUMPRODUCT(--(MONTH(B2:B5)=1),E2:E5+F2:F5,--(H2:H5="auto")) A couple of notes. If there are any empty cells in column B (within your range) then you need to add another array since an empty cell will evaluate as month 1: =SUMPRODUCT(--(ISNUMBER(B2:B5)),--(MONTH(B2:B5)=1),E2:E5+F2:F5,--(H2:H5="auto")) Also, you can't use entire columns as range references directly with SUMPRODUCT unless you're using Excel 2007.  SignatureBiff Microsoft Excel MVP >i have the following formula > =IF(MONTH($B:$B)=MONTH(J1),+SUMIF($H:$H,"auto",$F:$F)+SUMIF($H: [quoted text clipped - 12 lines] > > What am i missing here. Ooops! I forgot about this: ....=MONTH(J1) =SUMPRODUCT(--(MONTH(B2:B5)=MONTH(J1)),E2:E5+F2:F5,--(H2:H5="auto")) =SUMPRODUCT(--(ISNUMBER(B2:B5)),--(MONTH(B2:B5)=MONTH(J1)),E2:E5+F2:F5,--(H2:H5="auto")) SignatureBiff Microsoft Excel MVP > Try it like this: > [quoted text clipped - 26 lines] >> >> What am i missing here. > Try it like this: > [quoted text clipped - 36 lines] > > - Show quoted text - This worked perfect. Thank you so much. On Aug 13, 9:54 pm, "T. Valko" <biffinp...@comcast.net> wrote: > Try it like this: > [quoted text clipped - 36 lines] > > - Show quoted text - >This worked perfect. Thank you so much. You're welcome. Thanks for the feedback! SignatureBiff Microsoft Excel MVP |
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.