 |
 | 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 / Programming / March 2006conditional sum macro
Hello - I am trying to generate a report based on some usage reports. I have three columns, one with user names, one with usage description and the final with corresponding usage quantity. Some of the usage descriptions are synonymous so the usage quantity will be a sum of synonymous categories. For example: A B C Green X 4 Green Y 10 Green Z 5 Smith X 3 Smith Y 5 Smith Z 2 In this example, column B has the usage description and for our report we see X & Y as synonymous -- we would like to see usage results that are the sum of these. E.g. Green X/Y 14 Green Z 10 Smith X/Y 8 Smith Z 2 Etc. This seems like a pretty straightforward =SUMIF problem [=SUM(IF(A1:A$="Smith",IF(B1:B$="X,Y",C1:C$,0),0))] BUT there is of course a caveat... The list of names is over 100 and I'd prefer to NOT declare each one. Is there a way that I can use this formula where I look for unique values in column A to groupby and then sum accordingly? You could use SUMPRODUCT See explanations at http://www.xldynamic.com/source/xld.SUMPRODUCT.html http://mcgimpsey.com/excel/formulae/doubleneg.html I used 6 rows to check my formulas, just change row numbers as needed. You CANNOT use entire row (A:A) with SUMPRODUCT Without the synonym problem to start with, use =SUMPRODUCT(--(A1:A6="Green"),--(B1:B6="X"),C1:C6) When X and Y are to be aggregated =SUMPRODUCT(--(A1:A6="Green"),((B1:B6="X")+(B1:B6="Y")),C1:C6) or =SUMPRODUCT(--(A1:A6="Green"),--(B1:B6="X"),C1:C6)+SUMPRODUCT(--(A1:A6="Green"),--(B1:B6="Y"),C1:C6) But you do not want to enter names. Use a Pivot Table. To overcome X=Y (a) Use a helper column with =IF(OR(A1="X",A1="Y"), "X", A1) or (b) extract data from pivot table, adding the X and Y columns Not really a "macro" so why asked in this newsgroup rather than Worksheetfunctions ?  SignatureBernard V Liengme www.stfx.ca/people/bliengme remove caps from email > Hello - I am trying to generate a report based on some usage reports. I > have three columns, one with user names, one with usage description and [quoted text clipped - 25 lines] > declare each one. Is there a way that I can use this formula where I > look for unique values in column A to groupby and then sum accordingly? I would suggest using a pivot table. (this would produce all the combinations when used with the next suggestion) In the base data, I would create a dummy column that combines categories using a formula/lookup table. SignatureRegards, Tom Ogilvy > Hello - I am trying to generate a report based on some usage reports. I > have three columns, one with user names, one with usage description and [quoted text clipped - 25 lines] > declare each one. Is there a way that I can use this formula where I > look for unique values in column A to groupby and then sum accordingly? I just created a pivot table that does what you want. Insert a "calculated formula" equal to X+Y and hide the X and Y fields and it should work for you. hth, Doug > Hello - I am trying to generate a report based on some usage reports. I > have three columns, one with user names, one with usage description and [quoted text clipped - 25 lines] > declare each one. Is there a way that I can use this formula where I > look for unique values in column A to groupby and then sum accordingly? thanks for everyones help. The pivot table works great for this data. The only caveat now is this: my user data is supplied on a monthly basis - so I've created monthly pivot table / reports. I've been looking around this board to figure out how to combine these months to generate quarterly, semi anual, etc. reports. In particular, it would be nice to see what individual users are doing over a series of months. It looks like I can COMBINE these reports as long as the fields are the same, BUT I want to keep the data associated with the worksheet which in this case is associated with the month. When I try to setup a new pivot table / report from "multiple consolidation ranges" I browse for the appropriate work sheets and then get an error... Maybe I'm doing this wrong. Perhaps the data should not be structured? Any advice / help is greatly appreciated. Thanks! Hi, Take a look at Debra's brilliant site http://www.contextures.com/xlPivot08.html HTH Cheers Carim |
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.