 |
 | 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 2006Sumproduct or??
If I have two columns of numbers: 1 50 2 40 1 20 4 10 3 30 1 50 How do I write a formula that will sum every number in column B that corresponds to a 1 in column A. The answer should be 120. Thanks for any help. I believe you will want to use a sumif() function here if your data starts in A1 then =sumif(A1:A6,1,B1:B6) > If I have two columns of numbers: > [quoted text clipped - 11 lines] > > Thanks for any help. A pivot table would give you the solution you're looking for quite suitably. > If I have two columns of numbers: > [quoted text clipped - 11 lines] > > Thanks for any help. =sumif(a1:a10,1,b1:b10) or =sumif(a:a1,1,b:b) =sumproduct() will be useful when you add a second column (Column A has to be 1 and column C has to be "Dept 499") > If I have two columns of numbers: > [quoted text clipped - 11 lines] > > Thanks for any help.  SignatureDave Peterson >=sumif(a1:a10,1,b1:b10) >or [quoted text clipped - 18 lines] >> >> Thanks for any help. That worked! But I actually have 4 columns. I thought I could just drag it over. How would you write the formula if your data looked like this: 1 50 3 40 2 10 1 20 1 30 2 30 3 20 7 50 1 40 1 10 I want to sum everything with a 1 in column A and C. The answer should be 150. Thanks again you guys. Try =SUMIF(A1:A10,1,B1:B10)+SUMIF(C1:C10,1,D1:D10) SignatureRegards Roger Govier >>=sumif(a1:a10,1,b1:b10) >>or [quoted text clipped - 36 lines] > > Thanks again you guys. >Try > >=SUMIF(A1:A10,1,B1:B10)+SUMIF(C1:C10,1,D1:D10) Thanks again. One last question. With your help (and Dave Peterson) I now have this formula: =SUMIF($Y$5:$Y$34,1,$Z$5:$Z$34)+SUMIF($AA$5:$AA$34,1,$AB$5:$AB$34)+SUMIF($AC$5:$AC$34,1,$AD$34:$AD$54) Is threre a way to drag that down so I can count for the numbers 1 through 7? I would put the above formula in B1 to count all of the instances of 1, but then I would have to change all of the ones to twos for the second row, etc. Is there a way to write the formula so you can just drag it down? Thanks much. 1 2 3 4 5 6 7 >>>=sumif(a1:a10,1,b1:b10) >>>or [quoted text clipped - 36 lines] >> >> Thanks again you guys. Do you have a typo in the 3rd part of your formula, the Column AD references? As to automatically incrementing your criteria, try this, with AD amended: =SUMIF($Y$5:$Y$34,ROWS($1:1),$Z$5:$Z$34)+SUMIF($AA$5:$AA$34,ROWS($1:1),$AB$5:$AB$34)+SUMIF($AC$5:$AC$34,ROWS($1:1),$AD$5:$AD$34) SignatureHTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== On Fri, 8 Dec 2006 22:22:26 -0000, "Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote: >Try > >=SUMIF(A1:A10,1,B1:B10)+SUMIF(C1:C10,1,D1:D10) Thanks again. One last question. With your help (and Dave Peterson) I now have this formula: =SUMIF($Y$5:$Y$34,1,$Z$5:$Z$34)+SUMIF($AA$5:$AA$34,1,$AB$5:$AB$34)+SUMIF($AC$5:$AC$34,1,$AD$34:$AD$54) Is threre a way to drag that down so I can count for the numbers 1 through 7? I would put the above formula in B1 to count all of the instances of 1, but then I would have to change all of the ones to twos for the second row, etc. Is there a way to write the formula so you can just drag it down? Thanks much. 1 2 3 4 5 6 7 >> On Fri, 08 Dec 2006 09:29:47 -0600, Dave Peterson >> <petersod@verizonXSPAM.net> wrote: [quoted text clipped - 41 lines] >> >> Thanks again you guys. >Do you have a typo in the 3rd part of your formula, the Column AD >references? > >As to automatically incrementing your criteria, try this, with AD amended: > >=SUMIF($Y$5:$Y$34,ROWS($1:1),$Z$5:$Z$34)+SUMIF($AA$5:$AA$34,ROWS($1:1),$AB$5:$AB$34)+SUMIF($AC$5:$AC$34,ROWS($1:1),$AD$5:$AD$34) Yes, I see the typo. Thanks I'll try your formula. >Do you have a typo in the 3rd part of your formula, the Column AD >references? > >As to automatically incrementing your criteria, try this, with AD amended: > >=SUMIF($Y$5:$Y$34,ROWS($1:1),$Z$5:$Z$34)+SUMIF($AA$5:$AA$34,ROWS($1:1),$AB$5:$AB$34)+SUMIF($AC$5:$AC$34,ROWS($1:1),$AD$5:$AD$34) That worked great! Thanks, so much. (How do you guys know all this stuff?) Thanks for the feed-back. SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > > >Do you have a typo in the 3rd part of your formula, the Column AD [quoted text clipped - 9 lines] > > (How do you guys know all this stuff?) I'd just use two =sumif()'s. =sumif(a:a,1,b:b) + sumif(c:c,1,d:d) There was a typo in this line: =sumif(a:a1,1,b:b) A:A1 should have be A:A. > >=sumif(a1:a10,1,b1:b10) > >or [quoted text clipped - 34 lines] > > Thanks again you guys. 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.