Summarize Table By Category

  • Hi


    I have an Excel sheet which has Category Column, I have set an auto filter for it, When I click a particular Category data pertaining to that Category is visible.


    But my problem is I need help (VBA), that loops category autofilter, through each Category item and capture(sum number of items in that particular category) and put it in an other excel sheet. Kindly Help.


    regards
    Phi

  • Re: Category Autofilter


    Welcome to the forum. :)


    Has it got to be a VBA solution? As Mike says, if you know the categories, you can use a formula in the other sheet to get the number of items in each category on your first sheet. Can you attach a small sample workbook showing the layout and what you want the result to look like?

  • Re: Category Autofilter


    Presumably the categories are dynamic? Try this (with acknowledgements to Reafidy's MatchSkus()):


  • Re: Category Autofilter


    Thanks very much. Ackn: Reafidy's MatchSkus() Thanks very much.



    Well I think I am not clear in my explaination(sorry about that).


    Say I have a Sheet where it has Categories Like Meat, Dairy Products, Fruits, Vegetables. I need to calculate the number of units sold for each category and accmulate this in a table at the end of the day in an another excel sheet. I have set a autofilter for Category. It work fine!! But I have to go to each cateogry Instead of this can I automatically loop through the autofilter and calculate this and accumlate the same in the other excel sheet table?.


    regards

  • Re: Category Autofilter


    If your categories do not change from day to day then you do not need the Autofilter solution or my suggested macro.


    Say your database is in Sheet1 and your categories are in column A:A, then on a second sheet you just need a column, say A5:A20, with the list of categories and a second B5:B20 column with formulas - the formula is, as others have said, "=COUNTIF(Sheet1!$A$1:$A$400,A5)" in B5, and then paste down.

  • Re: Category Autofilter


    Quote from Greg.Com

    See the attached example where range 'Product' is cells A2:A23 and range 'Qty' is cells B2:B23 on the 'Data' sheet.


    HTH


    Thankx every one who have responded to this Thread.


    I am really getting very close to my target. Well I liked this EXAMPLE.XLS sheet because it exactly gives me the picture of what I am really up to, only one thing which needs to get added to this example is customer Column(Field). One person Can buy Meat and Vegetable etc. now how do I compute data for the customer ? for buying more than one products.


    Thanks a lot for all the help.


    regards

  • Re: Category Autofilter



    Have you tried using the Pivot Table functionality as Dave Hawley suggested ?

  • Re: Summarize Table of Data By Category


    I have not tried. To be frank I have not used one anytime. Can you suggest? (I am very very new to excel)


    Meanwhile in your Example.Xls "Product" & "Qty". How did you define that? coz.. in formula it shows (sumif(Products,A1,Qty) I have been trying the same thing in other sheets its just giving me 0 in the total column can you kindly elaborate this.

  • Re: Summarize Table of Data By Category


    The Pivot Table wizard in Excel can be found in Data -> Pivot Table & Pivot Chart Report from the menu bar and is very easy to use. You can probably find numerous examples on this forum also if you did a search.


    Regarding the formula in the example workbook, you need to define the ranges 'Product' (=Data!$A$2:$A$23) and 'Qty' (=Data!$B$2:$B$23) for the formula to work. Help on defining ranges can also be found in this forum also, I would imagine.

  • Re: Summarize Table of Data By Category


    Thanks Very much for your reply. Now I am very comfortable with defining ranges, Thanks very much for the help. I just want to know about the possibility of:


    1. In products column if you just type by mistake ( Meat) instead of (Meat) then it does not calculate for ( Meat) how to over come this in a large excel sheet there can be space before some words.


    2. Inserting a Column in your Excel Sheet as Customer and see the data Analysis done on Customer in relation with his purchase of products.


    Will sumif function work in this scenario ??

  • Re: Summarize Table of Data By Category



    1. You can use the TRIM function (using the formula wizard) to remove trailing spaces and you could also use Data -> Validation from the menu bar to only limit what data the user can input into the cells.


    2. It depends how you want to calculate the customer analysis. If you only have one criteria, i.e. just a sum of how many products the customer has purchased, you can use sumif. If you have more than one criteria, i.e. how many meat products a customer has purchased then you'll need either array formulas or database formulas such as dsum.


    Hope this helps and let me know if you need help with the formulas mentioned in point 2.


    Greg.

  • Re: Summarize Table of Data By Category


    Thanks Greg Thanks Soo much !!


    Yes, I need Help on the second Issue. To be more prescise I have attached a excel sheet which will give you a clear picture of what are the things I am looking at. its the same excel sheet which you gave me yesterday. I have modified it. Kindly have a look at it, on the analysis Sheet I have put some tables which will give you clear picture.


    I have encounterd a strange problem, results obtained in Autofilter (I mean sum(...)) is different when I use the formula Sumif(......) I don't know how to solve this.


    regards
    PHI

  • Re: Summarize Table of Data By Category



    Hi PHI,


    See attached spreadsheet. I've provided examples of both array formulas (these should only be used for small analysis tables as they re-calculate slowly and can make large workbooks unstable) and DSUM formulas (database formulas are more stable but require more work setting up due to the criteria tables and range naming).


    You may still want to consider investigating the use of Data -> Validation as there were a few spelling mistakes/typos that will cause the formulae to fail (I left TAMATO as it was !).


    N.B. Array formulas need to be entered using CTRL & SHIFT & ENTER for them to work but, once again, there are plenty of examples on this forum if you search for them.


    Hope this helps.


    Greg.

  • Re: Summarize Table of Data By Category


    Greg At this point I would like to ask you is it good to consider VBA + Autofilter option since Im working on a large excel sheet??


    As mentioned earlier I am not albe to find out why Autofilter(manual setting) + Subtotal works fine but my Sumif(...,...,...) gives me wrong total.???


    regards

  • Re: Summarize Table of Data By Category


    Have you checked that all your range names have been properly defined. I noticed in the last example that you posted that you hadn't defined ranges 'Category' & 'Customers' therefore your SumIf formula wouldn't calculate properly. Also, you need to make sure all the entries are spelt correctly, etc. Using the Data -> Validation would eradicate any spelling errors.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!