 |
 | 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 / February 2008Sumproduct, array, if to multiply mixed series of numbers
I have a long column of numbers like this: A B 1 Sales 123 2 GM% 35% 3 Other 234 4 Other 345 5 Sales 123 6 GM% 35% 7 Other 234 8 Other 345 9 Sales 123 10 GM% 35% 11 Other 234 12 Other 345 13 Sales 123 14 GM% 35% 15 Other 234 16 Other 345 and so on. Is there a formula I can use that does a sumproduct of all the sales numbers and all the GM%s, and ignores the rest? Thanks Try this: =SUM(SUMIF(A1:A16,{"Sales","GM%"},B1:B16))  SignatureBiff Microsoft Excel MVP >I have a long column of numbers like this: > [quoted text clipped - 22 lines] > > Thanks Thanks. That gives the sum of all "Sales" + sum of all "GM%". Any way to modify so that it gives the sumproduct? That is, I want a formula that gives (sales*GM% + sales*GM% + sales*GM %....) > Try this: > [quoted text clipped - 34 lines] > > > Thanks Assuming that Sales is always immediately followed by GM% then try this =SUMPRODUCT($B$1:$B$15,$B$2:$B$16,--($A$1:$A$15="Sales")) Charles __________________________________________________ Outlines for my Sessions at the Australia Excel Users Group http://www.decisionmodels.com/OZEUC.htm > Thanks. > [quoted text clipped - 42 lines] >> >> > Thanks That's the ticket - thanks. On Feb 9, 9:46 am, "Charles Williams" <Char...@DecisionModels.com> wrote: > Assuming that Sales is always immediately followed by GM% then try this > [quoted text clipped - 54 lines] > > >> > Thanks Hello, And maybe you would like to add a plausibility checks like =SUMPRODUCT($B$1:$B$15,$B$2:$B$16,--($A$1:$A$15="Sales"),--($A$2:$A $16="GM%")) =SUMPRODUCT($B$1:$B$15,$B$2:$B$16,--($A$2:$A$16="GM%")) If this does not show identical results then your data has some flaw in it...(unless these formulas are wrong :-) Regards, Bernd I guess I misunderstood your requirement. SignatureBiff Microsoft Excel MVP > Thanks. > [quoted text clipped - 42 lines] >> >> > Thanks =SUMPRODUCT((a1:a24={"GM%","sales"})*b1:b24) SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com >I have a long column of numbers like this: > [quoted text clipped - 22 lines] > > Thanks |
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.