+ Reply to Thread
Results 1 to 6 of 6

groupby, count and sum

  1. #1
    Registered User
    Join Date
    07-11-2013
    Location
    Lebanon
    MS-Off Ver
    Excel 2007
    Posts
    11

    groupby, count and sum

    Dear all,

    I have a sheet with 3 columns as following:
    Products, branch, amount

    a branch has many products
    so i want to groupby the products for each branch
    then count each product for each branch
    then do the sum of amount for each product in each branch

    for example:
    products nb-of-products sum-of-amount
    ice cream 5 27
    Chocolate 14 38
    Juice 7 12


    This table will be copied in a new sheet for each branch

    any ideas please?
    Thank you
    Attached Files Attached Files

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: groupby, count and sum

    You could use Pivot Tables.

    See attached for a very basic version.

    BSB
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    07-11-2013
    Location
    Lebanon
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: groupby, count and sum

    BadlySpelledBuoy thanks a lot but i want to avoid pivottables

    what i am trying to do is something like in an access query where you chose groupby in first field then you choose count in second field and last field you choose sum

    and i want the result to be copied as values only in the new sheet for each branch.

    I don't know if it can be done in excel !?

  4. #4
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: groupby, count and sum

    Perhaps you need to demonstrate what your desired output looks like and then we can help.

    Fair enough that you're not keen on using Pivot Tables (perhaps should have mentioned not wanting those in your original post!), but you don't state whether or not a VBA solution is an option for you. Is it?

    BSB

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: groupby, count and sum

    VBA
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    07-11-2013
    Location
    Lebanon
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: groupby, count and sum

    BadlySpelledBuoy i'm sorry for not mentioning that i don't want pivot tables and sorry again for not mentioning that i am looking for a vba code to do that... i posted my thread through my mobile and i was in hurry maybe that's why i didn't pay attention to the words i used trying to explain what i am looking for...
    anyway here is an attached picture which i hopefully will show you what i am trying to do with VBA code in excel.
    sorry again BadlySpelledBuoy

    jindon thank you for the code, i will try it asap and i will let you know if this is what i am looking for or not, now i just read it fast but thank you for your help
    Attached Images Attached Images

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro count items in sheet2 and provide count in embedded label in sheet1
    By jaredmccullough in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 02-05-2015, 11:27 AM
  2. GroupBy and distinct in excel
    By kumarsubram in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-11-2011, 08:18 PM
  3. Excel 2007 : groupby and distinct
    By kumarsubram in forum Excel General
    Replies: 1
    Last Post: 06-07-2011, 05:23 PM
  4. Replies: 1
    Last Post: 04-12-2011, 07:00 PM
  5. Mimicking an GroupBy Clause in Excel
    By GreyPilgrim in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-01-2006, 05:40 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1