 |
 | 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 / June 2004sumproduct with multiple criteria
is something like this possible (multiple criteria in the braces)? I know it does not work currently, but I am hoping there is a way to do something like this without adding 2 sumproduct formulas. =SUMPRODUCT(--(QUERY!$A$2:$A$31311={$B16,$C16}),--(QUERY!$F$2:$F$31311=J$3), --(QUERY!$G$2:$G$31311<=T50MONTH),--(QUERY!$H$2:$H$31311)) TIA, -- ============================== ryanb. xl 2002, access 2002, greatplains 7.5 ============================== It looks like you want a count where all criteria are met. I can't tell what the -- is getting at unless it's for legibility, but assuming it's not trying to do something unexpected and that the multiple criteria is an OR situation, then this should work (with some extra spaces) ={SUM(((QUERY!$A$2:$A$31311=$B16)+(QUERY!$A$2:$A$31311=$C16)) * (QUERY!$F$2:$F$31311=J$3) * (QUERY!$G$2:$G$31311<=T50MONTH) * (QUERY!$H$2:$H$31311)) * IF(B16=C16,.5,1)} *if(B16=C16,.5,1) at the end prevents the double count if B16=C16. {} indicates an array formula. If this doesn't help, I would need an explanation of what the formula is trying to do. ken c > is something like this possible (multiple criteria in the braces)? I know > it does not work currently, but I am hoping there is a way to do something > like this without adding 2 sumproduct formulas. =SUMPRODUCT(--(QUERY!$A$2:$A$31311={$B16,$C16}),--(QUERY!$F$2:$F$31311=J$3), > --(QUERY!$G$2:$G$31311<=T50MONTH),--(QUERY!$H$2:$H$31311)) > [quoted text clipped - 5 lines] > xl 2002, access 2002, greatplains 7.5 > ============================== What's not working. The {b16,c16} just means that if your A range has either then OK to count it? same thing as writing ((QUERY!$A$2:$A$31311=$B16)+(QUERY!$A$2:$A$31311=$c16)). Are you trying to get a month out the G range formatted as dates. If so use month(QUERY!$G$2:$G$31311)<=T50MONTH Try breaking your formula down into each component to test.  SignatureDon Guillett SalesAid Software donaldb@281.com > is something like this possible (multiple criteria in the braces)? I know > it does not work currently, but I am hoping there is a way to do something > like this without adding 2 sumproduct formulas. =SUMPRODUCT(--(QUERY!$A$2:$A$31311={$B16,$C16}),--(QUERY!$F$2:$F$31311=J$3), > --(QUERY!$G$2:$G$31311<=T50MONTH),--(QUERY!$H$2:$H$31311)) > [quoted text clipped - 5 lines] > xl 2002, access 2002, greatplains 7.5 > ============================== I don't mean to hijack the thread, but your response is interesting, and confusing. In what context can {b16,c16} be used like an OR? If I make a very simple example A2=2 B2=2 A3=3 B3=5 A4=4 and in B4, I want a count for the number of time B2 or B3 appear in the range A2:A4 Depending on my mood, I might use: =COUNTIF(A2:A4,B2)+COUNTIF(A2:A4,B3) -or- ={SUM((A2:A4=B2)*1,(A2:A4=B3)*1)} Or maybe if I was testing only one set on criteria at a time, I would add a label in A1 and B1 and use =DCOUNT(A1:A4,A1,B1:B3) but I cant think of a way to use {B2,B3} in any test condition. > What's not working. The {b16,c16} just means that if your A range has either > then OK to count it? > same thing as writing > ((QUERY!$A$2:$A$31311=$B16)+(QUERY!$A$2:$A$31311=$c16)). You can use =SUM(--(A2:A4=TRANSPOSE(B2:B3))) entered with ctrl + shift & enter or entered normally like =SUMPRODUCT(COUNTIF(A2:A4,B2:B3)) or you can hard code it like =SUMPRODUCT(--(A2:A4={2,5})) entered normally SignatureRegards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) > I don't mean to hijack the thread, but your response is interesting, and > confusing. [quoted text clipped - 20 lines] > > same thing as writing > > ((QUERY!$A$2:$A$31311=$B16)+(QUERY!$A$2:$A$31311=$c16)). You learn something new everyday. I didn't know that sumproduct could work on array formulas without being entered with ctrl+shift+enter. Are there other functions for which that is true? > You can use > [quoted text clipped - 37 lines] > > > same thing as writing > > > ((QUERY!$A$2:$A$31311=$B16)+(QUERY!$A$2:$A$31311=$c16)). > You learn something new everyday. > I didn't know that sumproduct could work on array formulas > without being entered with ctrl+shift+enter. > Are there other functions for which that is true? Yes indeed; many. INDEX, MATCH, SUM, MAX and MIN are a few that come readily to mind. Alan Beban Seems to be true only part of the time. From the example I showed: =SUMPRODUCT(--(A2:A4={2,5})) works, but =SUM(--(A2:A4={2,5})) doesn't work (unless you use ctrl+shift+enter). On the other hand, =SUM(--(TRANSPOSE({2,3,4})={2,5})) does work. I can't get SUM to work with arrays unless the formula only contains constants. I can't think of examples of when I would use array formulas with the other functions listed. > > You learn something new everyday. > > I didn't know that sumproduct could work on array formulas [quoted text clipped - 5 lines] > > Alan Beban > I can't get SUM to work with arrays unless the formula only contains > constants. > I can't think of examples of when I would use array formulas with the other > functions listed. With names (Including Smith) in A2:A6 and weekly scores in B2:E6, how would you return the maximum score for any particular name (e.g., Smith)? Alan Beban Don, Currently I am using the following formula and it works, thought the worksheet is extremely slow (the formula is in 4 columns on approximately 1300 rows... two columns <=T50MONTH and two =T50MONTH). I saw a response to another sumproduct question where {} were used for multiple criteria. I was curious as to whether the {} could be used to reference multiple cells. =SUMPRODUCT(--(QUERY!$A$2:$A$31311=$B16),--(QUERY!$F$2:$F$31311=J$3),--(QUER Y!$G$2:$G$31311<=T50MONTH),--(QUERY!$H$2:$H$31311))+SUMPRODUCT(--(QUERY!$A$2 :$A$31311=$C16),--(QUERY!$F$2:$F$31311=J$3),--(QUERY!$G$2:$G$31311<=T50MONTH ),--(QUERY!$H$2:$H$31311)) This works fine (but slow), but I was wondering if there was a way to do it without adding sumproducts, thus possibly speeding it up a bit. When I tried to use: =SUMPRODUCT(--(QUERY!$A$2:$A$31311={$B16,$C16}),--(QUERY!$F$2:$F$31311=J$3), --(QUERY!$G$2:$G$31311<=T50MONTH),--(QUERY!$H$2:$H$31311)) it does not work, and I am guessing it is because I am not using the {}correctly, though I am not very familiar with {} and cannot be sure. The formula sums a customers sales by month by year, and each customer has 2 customer id's (one for product purchases, and a separate account for large purchases put on a note: columns B and C). Row 3 contains the year for each column. Column H on the QUERY tab contains the sales dollars. Thanks your help in advance, -- ============================== ryanb. xl 2002, access 2002, greatplains 7.5 ============================== > What's not working. The {b16,c16} just means that if your A range has either > then OK to count it? [quoted text clipped - 12 lines] > > it does not work currently, but I am hoping there is a way to do something > > like this without adding 2 sumproduct formulas. =SUMPRODUCT(--(QUERY!$A$2:$A$31311={$B16,$C16}),--(QUERY!$F$2:$F$31311=J$3), > > --(QUERY!$G$2:$G$31311<=T50MONTH),--(QUERY!$H$2:$H$31311)) > > [quoted text clipped - 5 lines] > > xl 2002, access 2002, greatplains 7.5 > > ============================== |
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.