 |
 | 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 2008dynamic range? help!
tab1 salesperson 1/31/2007 2/28/2007 3/31/2007 XYZ ? ? ? tab2 salesperson date cont value XYZ 1/15/2007 500 XYZ 1/31/2007 1000 XYZ 1/10/2007 850 trying to setup a sumproduct to return XYZ total contract value for January (that obviously spans over multiple dates). can you assist me w/ a formula solution to this problem? thanks!! Kyle try this idea =sumproduct((month(b2:b22)=1)*(a2:a22="xyz")*c2:c22)  SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > tab1 > [quoted text clipped - 13 lines] > > Kyle what if i need not only specific month, but also specific year? thx!! > try this idea > =sumproduct((month(b2:b22)=1)*(a2:a22="xyz")*c2:c22) [quoted text clipped - 21 lines] > > > Kyle add another parameter. SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > what if i need not only specific month, but also specific year? thx!! > [quoted text clipped - 27 lines] >> >> > Kyle ok i'm trying to use this formula: =SUMPRODUCT(--(MONTH(Revenue!$V$9:$V $3800)=MONTH($N$9)), --(YEAR(Revenue!$V$9:$V$3800)=YEAR($N$9)),--(Revenue!$D$9:$D $3800=B26),--(Revenue!$AD$9:$AD$3800)) BUT i'm getting a #VALUE error msg. any ideas? thx!! > add another parameter. > [quoted text clipped - 34 lines] > > >> > Kyle try this That suggests that some of the data is bad, maybe text in one of the date fields or a cell with a formula erroring. Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > ok i'm trying to use this formula: =SUMPRODUCT(--(MONTH(Revenue!$V$9:$V > $3800)=MONTH($N$9)), [quoted text clipped - 47 lines] >> >> >> > Kyle I didn't see where you kept the xyz stuff: =sumproduct(--(revenue!$a$9:$a$3800="xyz"), --(text(revenue!$v$9:$v$3800,"yyyymm")=text($n$9,"yyyymm")), revenue!$ad$9:$ad$3800) The --(revenue!$ad$9:$ad$3800) portion will cause an error if there are non-numeric entries in that range. Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail here: http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html > ok i'm trying to use this formula: =SUMPRODUCT(--(MONTH(Revenue!$V$9:$V > $3800)=MONTH($N$9)), [quoted text clipped - 41 lines] > > > > >> > Kyle SignatureDave Peterson
|
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.