 |
 | 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 / October 2007SUMPRODUCT
Can anyone help me making use of the Formula "SUMPRODUCT" within the below file? http://www.flypicture.com/download/MzcyMjA= Thank you very much Gennaro Very few contributors will download a file from an unknown source, so you might not get much help. Can you describe what it is you are trying to do and give examples of your formula - explain how it is not working (error, wrong result etc) Hope this helps. Pete > Can anyone help me making use of the Formula "SUMPRODUCT" within the below > file? [quoted text clipped - 3 lines] > Thank you very much > Gennaro Dear Pete - thank you for your reply. The sourse in Sheet 1 (Download) says: A B C 000001 00001001 -32644.52 The result in Sheet 2 says: A B C 000001 00001001 =SUMPRODUCT(--(Download!A:A=A2)*(Download!B:B=B2)*(Download!C:C)) Hope this is not too confusing. Regards Gennaro > Very few contributors will download a file from an unknown source, so > you might not get much help. Can you describe what it is you are [quoted text clipped - 12 lines] > > Thank you very much > > Gennaro You can't use whole columns in SUMPRODUCT() functions. Use a smaller range which is bigger then you will ever use but still short of a full column.  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 > Dear Pete - thank you for your reply. > [quoted text clipped - 29 lines] >> > Thank you very much >> > Gennaro I haven't looked at your file, but ... at first glance ... unless you're using XL07, your formula *cannot* contain entire column references (A:A, B:B, ...etc.). Start off by changing that in your formula to realistic range sizes, and see what happens. SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > Dear Pete - thank you for your reply. > [quoted text clipped - 5 lines] > A B C > 000001 00001001 =SUMPRODUCT(--(Download!A:A=A2)*(Download!B:B=B2)*(Download!C:C)) > Hope this is not too confusing. > [quoted text clipped - 17 lines] > > > Thank you very much > > > Gennaro Thank you to Sandy and Ragdyer. I've now changed the range sizes and the result returned by the formula is equal to #VALUE! Not sure what elase i can possibly try! Regards Gennaro > I haven't looked at your file, but ... at first glance ... unless you're > using XL07, your formula *cannot* contain entire column references (A:A, [quoted text clipped - 36 lines] > > > > Thank you very much > > > > Gennaro Usually when I get #VALUE! errors it is because I am trying to do maths on text. Do you have text in any of your ranges. If they look like numbers you can test with =ISTEXT(A1) and copy down & across. 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 > Thank you to Sandy and Ragdyer. I've now changed the range sizes and the > result returned by the formula is equal to #VALUE! [quoted text clipped - 49 lines] >> > > > Thank you very much >> > > > Gennaro Thank you Sandy - How shall i insert the "ISTEXT" fomula within SUMPRODUCT formula? I'm not sure how to link both formula. Thank you again, Gennaro > Usually when I get #VALUE! errors it is because I am trying to do maths on > text. Do you have text in any of your ranges. If they look like numbers [quoted text clipped - 53 lines] > >> > > > Thank you very much > >> > > > Gennaro =SUMPRODUCT(--(Download!A2:A4000=Sheet2!A2),--(Download!B2:B4000=B2),(Download!C2:C4000)) SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Thank you Sandy - How shall i insert the "ISTEXT" fomula within SUMPRODUCT > formula? I'm not sure how to link both formula. [quoted text clipped - 64 lines] >> >> > > > Thank you very much >> >> > > > Gennaro Using the asterisk form of Sumproduct, =SUMPRODUCT((Download!A1:A100=A2)*(Download!B1:B100=B2)*Download!C1:C100) *ANY* alpha characters in the calculating range (Column C), will cause the #Value! error, BUT ... any text numbers will *still* calculate *accurately*. If it looks like a number, the asterisk form will calculate it. Also, if you are populating your calc range (Column C) with formulas that equate to a zero length string ( "" ), this will also cause the #Value! error. You can try revising your formula to the unary form, =SUMPRODUCT(--(Download!A1:A100=A2),--(Download!B1:B100=B2),Download!C1:C100 ) which will by-pass alpha and null entries. Note, the unary form will by-pass text numbers also with no notification. It will just calculate *true XL* numbers only. SignatureHTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > Thank you to Sandy and Ragdyer. I've now changed the range sizes and the > result returned by the formula is equal to #VALUE! [quoted text clipped - 48 lines] > > > > > Thank you very much > > > > > Gennaro Dear Ragdyer - Thank you for your help. Your latest suggestion does work but somehow it’s not quite right. Can you be kind and have a look at the file i've sent via email? It would be greatly appreciated. Regards Gennaro > Using the asterisk form of Sumproduct, > [quoted text clipped - 78 lines] > > > > > > Thank you very much > > > > > > Gennaro Besides the fact that I have not received any e-mails on this subject, it's usually standard procedure in these groups to *first* ask permission, especially since my signature includes a statement to the fact of keeping the discussions public. However, if you wish, you can send a *small* file. Just cut out cutout from my address. SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > Dear Ragdyer - Thank you for your help. Your latest suggestion does work > but [quoted text clipped - 96 lines] >> > > > > > Thank you very much >> > > > > > Gennaro Besides what has been said, if you want to count use =SUMPRODUCT((Download!A2:A22=A2)*(Download!B2:B22=B2)*(Download!C2:C22)) to SUM c based on a & b use =SUMPRODUCT((Download!A2:A22=A2)*(Download!B2:B22=B2)*Download!C2:C22) SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Dear Pete - thank you for your reply. > [quoted text clipped - 29 lines] >> > Thank you very much >> > Gennaro Hey Don, some typos? Both formulas are identical as far as calculations are concerned. Both will total Column C. Counting might be: =SUMPRODUCT((Download!A2:A22=A2)*(Download!B2:B22=B2)*(Download!C2:C22<>"")) OR, simply: =SUMPRODUCT((Download!A2:A22=A2)*(Download!B2:B22=B2)) SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > Besides what has been said, if you want to count use > =SUMPRODUCT((Download!A2:A22=A2)*(Download!B2:B22=B2)*(Download!C2:C22)) [quoted text clipped - 34 lines] > >> > Thank you very much > >> > Gennaro It was late =SUMPRODUCT((Download!A2:A22=A2)*(Download!B2:B22=B2)) count SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > Hey Don, some typos? > [quoted text clipped - 45 lines] >> >> > Thank you very much >> >> > Gennaro Maybe you mean in C2 in Sheet2: =SUMPRODUCT((Download!$A$2:$A$3824=$A2)*(Download!$B$2:$B$3824=$B2),Download!C$2:C$3824) with C2 then copied across and filled down to populate SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- Max - Thank you very very much. You have been once again very helpful. Kind regards Gennaro > Maybe you mean in C2 in Sheet2: > =SUMPRODUCT((Download!$A$2:$A$3824=$A2)*(Download!$B$2:$B$3824=$B2),Download!C$2:C$3824) > with C2 then copied across and filled down to populate |
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.