 |
 | 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 / December 2007sumif
still trying to find a way to use 2 criteria within 2 ranges to add 1 range of data; range1 range2 criteria1 criteria2 data =sumif(range1,criteria1,data)+sumif(range2,criteria2,data) will give twice the amount Hi Sulasno, I assume you want to sum the data only where range1=criteria1 AND range2=criteria2. SUMIF won't work with multiple conditions in that way. For that you could use the SUMPRODUCT function (see the other replies), or: =SUM(IF((range1=criteria1)*((range2=criteria2),data)) as an array formula (entered with <Ctrl>-<Shift>-<Enter>). Note that both SUMPRODUCT and the array formula above require a fully-qualified range - you can't use simple whole-column or whole-row references (ie E:E or 3:3 won't work). Cheers  Signaturemacropod [MVP - Microsoft Word] ------------------------- > still trying to find a way to use 2 criteria within 2 ranges to add 1 range of data; > > range1 range2 criteria1 criteria2 data > > =sumif(range1,criteria1,data)+sumif(range2,criteria2,data) will give twice the amount thanks I am trying to understand what does the * means going to try this forumula; =sum(if((A1:A100,"XXX")*((B1:B100,"YYY"),C1:C100)) and use <Ctrl>-<Shift>-<Enter>) can "XXX" and "YYY" point to a value in a cell? =sum(if((A1:A100,X1((B1:B100,Y1),C1:C100)) and use <Ctrl>-<Shift>-<Enter>) > Hi Sulasno, > [quoted text clipped - 17 lines] >> =sumif(range1,criteria1,data)+sumif(range2,criteria2,data) will give >> twice the amount Hi Sulasno, The tests (range1=criteria1) and (range2=criteria2) both return True/False (1/0) values according to whether the test is satisfied. So each test ends up with and array of 1s and 0s. The * multiples the 1s and 0s from the two arrays. The result is that you end up with 1s (ie True results) where the values in both arrays are 1s (ie 1*1=1, 1*0=0, 0*1=0, 0*0=0). In essence, then, * works like an AND test - basically requiring both conditions to be true. Sorry about the typo in the formula. It should have been: =SUM(IF((range1=criteria1)*(range2=criteria2),data)) And yes, you can add more conditions, for example: =SUM(IF((range1=criteria1)*(range2=criteria2)*(range2>criteria1)*(range1<>criteria2),data)) Cheers Signaturemacropod [MVP - Microsoft Word] ------------------------- > thanks > I am trying to understand what does the * means [quoted text clipped - 22 lines] >>> >>> =sumif(range1,criteria1,data)+sumif(range2,criteria2,data) will give twice the amount thanks finally got it right {=SUM(IF(($A2:$A7=A2)*($B$2:$B$7=B2),$E$2:$E$7))} > Hi Sulasno, > [quoted text clipped - 17 lines] >> =sumif(range1,criteria1,data)+sumif(range2,criteria2,data) will give >> twice the amount Less resource use with sumproduct. =SUMproduct(($A2:$A$7=A2)*($B$2:$B$7=B2),$E$2:$E$7) SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > thanks > finally got it right [quoted text clipped - 22 lines] >>> =sumif(range1,criteria1,data)+sumif(range2,criteria2,data) will give >>> twice the amount thanks is there a limit to the number of criteria? I presume that this will also work? =SUMPRODUCT(($A$2:$A$7=A2)*($B$2:$B$7=B2)*($C$2:$C$7=C2)*($D$2:$D$7=D2),$E$2:$E$7) > Less resource use with sumproduct. > =SUMproduct(($A2:$A$7=A2)*($B$2:$B$7=B2),$E$2:$E$7) [quoted text clipped - 25 lines] >>>> =sumif(range1,criteria1,data)+sumif(range2,criteria2,data) will give >>>> twice the amount You should always try it yourself first. SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > thanks > is there a limit to the number of criteria? [quoted text clipped - 31 lines] >>>>> =sumif(range1,criteria1,data)+sumif(range2,criteria2,data) will give >>>>> twice the amount I wish I could; I don't have the file in hand and is helping someone with the question > You should always try it yourself first. >> thanks [quoted text clipped - 32 lines] >>>>>> =sumif(range1,criteria1,data)+sumif(range2,criteria2,data) will give >>>>>> twice the amount
|
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.