 |
 | 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 / January 2008SUMPRODUCT to test two conditions
In my checking-account spreadsheet, I have A4 and below - date E4 and below - deposit amount H4 and below - comment B2 - today's date or as-of date (Rows 1 to 3 of columns A and E are just text headers, not numbers or formulas.) I want to summarize interest for the current year (year that matches B2's year, comment equals "Interest"). The following array formula works, but I have to remember to update it when transactions go below row 699: { =SUM( (YEAR(A4:A699)=YEAR(B$2))*(H4:H699="Interest")*E4:E699 ) } It seems this should be doable with a sumproduct, so I type: =SUMPRODUCT(E:E, --(YEAR(A:A)=YEAR($B$2)), --(H:H="Interest")) but I get a #NUM. When I change it to =SUMPRODUCT(E4:E, --(YEAR(A4:A)=YEAR($B$2)), --(H4:H="Interest")) I get #NAME. Making the references absolute didn't help: =SUMPRODUCT($E:$E, --(YEAR($A:$A)=YEAR($B$2)), --($H:$H="Interest")) still gets #NUM. What am I doing wrong, and what's the solution? Thanks!  SignatureStan Brown, Oak Road Systems, Tompkins County, New York, USA http://OakRoadSystems.com/ "If there's one thing I know, it's men. I ought to: it's been my life work." -- Marie Dressler, in /Dinner at Eight/ SUMPROUCT(), like array formulas, cannot work with whole columns. Try making the whole columns ranges even if it is B4:A65535 but having such a large range may slow your sheet down somewhat. SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > In my checking-account spreadsheet, I have > A4 and below - date [quoted text clipped - 28 lines] > > What am I doing wrong, and what's the solution? Thanks! Sun, 13 Jan 2008 13:56:37 -0000 from Sandy Mann <sandymann2 @mailinator.com>: > SUMPROUCT(), like array formulas, cannot work with whole columns. Try > making the whole columns ranges even if it is B4:A65535 but having such a > large range may slow your sheet down somewhat. Thanks. I had thought about that but like you I was concerned about performance. I didn't benchmark it, though. T. Valko's suggestion of dynamic ranges did the trick. SignatureStan Brown, Oak Road Systems, Tompkins County, New York, USA http://OakRoadSystems.com/ "If there's one thing I know, it's men. I ought to: it's been my life work." -- Marie Dressler, in /Dinner at Eight/ try this =SUMPRODUCT(--(YEAR(A2:A22)=YEAR($B$2),--(H2:H22="Interest"),e2:e22) or =SUMPRODUCT((YEAR(A2:A22)=YEAR($B$2)*(H2:H22="Interest")*e2:e22) SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > In my checking-account spreadsheet, I have > A4 and below - date [quoted text clipped - 28 lines] > > What am I doing wrong, and what's the solution? Thanks! >I have to remember to update it when transactions go below row 699: You can't use entire columns as range references unless you're using Excel 2007. You can either use a range large enough to allow for future entries or you could use a dynamic range. A dynamic range would be the best choice. http://contextures.com/xlNames01.html#Dynamic SignatureBiff Microsoft Excel MVP > In my checking-account spreadsheet, I have > A4 and below - date [quoted text clipped - 28 lines] > > What am I doing wrong, and what's the solution? Thanks! Sun, 13 Jan 2008 14:34:32 -0500 from T. Valko <biffinpitt@comcast.net>: > Subject: Re: SUMPRODUCT to test two conditions > Newsgroups: microsoft.public.excel [quoted text clipped - 6 lines] > You can't use entire columns as range references unless you're using Excel > 2007. Ah -- that was the piece I was missing. Thanks! Any chance you can remove that "-- " signature delimiter? It's appropriate when what follows is just signature, but since you're posting your comments and your signature before the quoted material that makes the whole quote get treated like part of your signature. Newsreaders that honor the delimiter then throw away the entire previous quote because signatures aren't supposed to be quoted. SignatureStan Brown, Oak Road Systems, Tompkins County, New York, USA http://OakRoadSystems.com/ "If there's one thing I know, it's men. I ought to: it's been my life work." -- Marie Dressler, in /Dinner at Eight/ >Any chance you can remove that "-- " signature delimiter? I could....if I knew how. It looks like OE automatically inserts that into the sig. I could manually remove it every time I post but then I'd get "tired" of doing that since I post quite a bit. Biff Microsoft Excel MVP > Sun, 13 Jan 2008 14:34:32 -0500 from T. Valko > <biffinpitt@comcast.net>: [quoted text clipped - 18 lines] > Newsreaders that honor the delimiter then throw away the entire > previous quote because signatures aren't supposed to be quoted. Sun, 13 Jan 2008 17:03:56 -0500 from T. Valko <biffinpitt@comcast.net>: > >Any chance you can remove that "-- " signature delimiter? > > I could....if I knew how. It looks like OE automatically inserts that into > the sig. I could manually remove it every time I post but then I'd get > "tired" of doing that since I post quite a bit. Thanks for the reply. I don't use OE so I can't advise you. Sigh. I get SO tired of Microsoft software subverting Internet standards. SignatureStan Brown, Oak Road Systems, Tompkins County, New York, USA http://OakRoadSystems.com/ "If there's one thing I know, it's men. I ought to: it's been my life work." -- Marie Dressler, in /Dinner at Eight/ I never realized that there was a purpose to those dashes ... until now. If you want to go to the trouble, I'm told by one of the OE groups that the only way is to use 3rd party software. Here's a free, recommended one - OE-QuoteFix http://home.in.tum.de/~jain/software/oe-quotefix/ SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > >Any chance you can remove that "-- " signature delimiter? > [quoted text clipped - 27 lines] >> Newsreaders that honor the delimiter then throw away the entire >> previous quote because signatures aren't supposed to be quoted. >If you want to go to the trouble Nah, not really. I was thinking about looking in the OE groups for info on the subject. Do you have a link to a thread? SignatureBiff Microsoft Excel MVP >I never realized that there was a purpose to those dashes ... until now. > [quoted text clipped - 36 lines] >>> Newsreaders that honor the delimiter then throw away the entire >>> previous quote because signatures aren't supposed to be quoted. It seems the one I started pertaining to your question in this thread kinda grew a little over there. They do get into discussions ... much the same as happens here: http://tinyurl.com/369pwo SignatureRegards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- >If you want to go to the trouble Nah, not really. I was thinking about looking in the OE groups for info on the subject. Do you have a link to a thread? SignatureBiff Microsoft Excel MVP >I never realized that there was a purpose to those dashes ... until now. > [quoted text clipped - 36 lines] >>> Newsreaders that honor the delimiter then throw away the entire >>> previous quote because signatures aren't supposed to be quoted. >They do get into discussions ... much the same as happens here: I see! Thanks for the link. Well, I'm not going to change anything. If "my" sig messes up someone's newsreader experience......oh well! This little inconvenience is the same as top post vs bottom post. SignatureBiff Microsoft Excel MVP > It seems the one I started pertaining to your question in this thread > kinda [quoted text clipped - 49 lines] >>>> Newsreaders that honor the delimiter then throw away the entire >>>> previous quote because signatures aren't supposed to be quoted. |
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.