+ Reply to Thread
Results 1 to 5 of 5

VBA code advice/Excel advice for summing groups of numbers

  1. #1
    Registered User
    Join Date
    07-16-2014
    Location
    London, England
    MS-Off Ver
    Office 2010
    Posts
    4

    VBA code advice/Excel advice for summing groups of numbers

    Product Group Arg. Braz. Mex. Peru
    Red paint Paint 5 3 2 4
    Blue paint Paint 5 6 8 4
    Wallpaper Wallpaper 6 5 8 5
    Lamps Furniture 4 6 3 1
    Chairs Furniture 1 6 4 5
    Green paint Paint 1 3 5 6
    Sofas Furniture 3 3 5 8



    SUM(Paint,Argentina) SUM(Paint,Brazil) SUM(Paint,Mexico) SUM(Paint,Peru)
    SUM(Wallpaper,Argentina) SUM(Wallpaper,Brazil) SUM(Wallpaper,Mexico) SUM(Wallpaper,Peru)
    SUM(Furniture,Argentina) SUM(Furniture,Brazil) SUM(Furniture,Mexico) SUM(Furniture,Peru)

    I am trying to work out VBA code or another way in Excel to find a solution for this problem - I need to be able to sum up the values that I have posted below - so for SUM(Paint,Argentina), this would be the sum of the red paint, blue paint and green paint values under the Argentina heading, that are all categorised into the 'Paint' group. The same follows for all the other categories. I was trying to use 'Defined Names' to group the categories together, and so would then do =Sum(Paint). However this was not allowing me to sum by each country, and the 'Create from Selection' naming tool was not naming all of the values for Paint (blue paint, red paint, etc.) under the paint category.

    This is an incredibly simplified version of the data I am actually dealing with - I am doing this for 70 country columns and 250 rows of product categories. But the same principle applies. Many thanks!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: VBA code advice/Excel advice for summing groups of numbers

    SUMIFS will do what you want.
    Hope this helps

    Sometimes its best to start at the beginning and learn VBA & Excel.

    Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
    Available for remote consultancy work PM me

  3. #3
    Registered User
    Join Date
    07-16-2014
    Location
    London, England
    MS-Off Ver
    Office 2010
    Posts
    4

    Re: VBA code advice/Excel advice for summing groups of numbers

    Thanks for the quick reply. I'm afraid I still don't follow what criteria I would need to put in to SUMIFS?

  4. #4
    Registered User
    Join Date
    07-16-2014
    Location
    London, England
    MS-Off Ver
    Office 2010
    Posts
    4

    Re: VBA code advice/Excel advice for summing groups of numbers

    I've worked it out. Many thanks

  5. #5
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: VBA code advice/Excel advice for summing groups of numbers

    Format your data range as a Table, then use formulae like:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    etc
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

+ 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. [SOLVED] Some advice on how to improve my code if you please :)
    By r2fro in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-09-2014, 05:14 AM
  2. Advice on code
    By Greg in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-10-2006, 11:55 AM
  3. Need some advice on the following code
    By Dean in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-02-2006, 09:10 PM
  4. Little more advice on this code
    By Greg B in forum Excel General
    Replies: 3
    Last Post: 09-03-2005, 01:05 AM
  5. [SOLVED] Little more advice on this code
    By Greg B in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-03-2005, 01:05 AM

Tags for this Thread

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