+ Reply to Thread
Results 1 to 9 of 9

Summarizing data

  1. #1
    Forum Contributor
    Join Date
    03-16-2013
    Location
    Florida
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    132

    Summarizing data

    Hello

    I'm trying to figure out how to create a complex Macro. I have attached a sample spreadsheet that I hope can explain what I'm trying to do. You will see I have inserted comments on the attached spreadsheet in hopes to clearly spell it out.

    If one of the experts here wants to tackle this let me know and I will send it over to the Commercial Services area. Just message me how many points you need to tackle it.

    Thanks





    Needed Macro.xlsx

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Summarizing data

    No VBA is needed for any of this.

    1) Sheet1 data does not need to be sorted. The data can be in any random order, as long as the values in column A are consistent, Sheet2 will still work.

    2) Sheet2 is your list of categories in column A and a simple SUMIF() formula in column B. As you edit/replace the data on Sheet1, this sheet will updates its sums.


    NOTE: This would work even if Sheet2 is moved to a separate workbook.
    Attached Files Attached Files
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Forum Contributor
    Join Date
    03-16-2013
    Location
    Florida
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    132

    Re: Summarizing data

    Thanks JBeaucaire

    Truly do appreciate it. Love how it works may have a few more questions as I get more into it.

    I'm trying to learn would you mind explaining what the code you wrote means I'm seeing somethings I never seen before and it would be very helpful if you could explain it.

    It works perfect just trying to figure out what you did

    Please Login or Register  to view this content.
    Thanks

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Summarizing data

    SUMIF() is a basic Excel function.... sum the numbers in one column if the values in an adjacent column match a criteria:

    =SUMIF(SearchRange, Criteria, SumRange)

    Next, I slipped that into an IF/THEN/ELSE model so that formula only calculates if the cell in column A is not blank. That reads:

    IF A2 IS EMPTY, SHOW A BLANK HERE, ELSE DO THE SUMIF ON THE VALUE IN A2 AGAINST THE ENTIRE COLUMN A SUMMING VALUES IN COLUMN B.

  5. #5
    Forum Contributor
    Join Date
    03-16-2013
    Location
    Florida
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    132

    Re: Summarizing data

    thanks for the explanation greatly appreciated.

    I will have some more questions I think as I get more into this. Should I mark this solved and then come back to same post and ask the questions in reply of can I private message you?

    Thanks again for the help.

  6. #6
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Summarizing data

    This is a public forum, so all questions should be posted in public threads where any of our answerers can pipe in to assist and others can read/research these topics and benefit from the public discussion.

    Private Messaes should never be used to further a discussion that should be in the open forum. Thanks.

  7. #7
    Forum Contributor
    Join Date
    03-16-2013
    Location
    Florida
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    132

    Re: Summarizing data

    Quote Originally Posted by JBeaucaire View Post
    SUMIF() is a basic Excel function.... sum the numbers in one column if the values in an adjacent column match a criteria:

    =SUMIF(SearchRange, Criteria, SumRange)

    Next, I slipped that into an IF/THEN/ELSE model so that formula only calculates if the cell in column A is not blank. That reads:

    IF A2 IS EMPTY, SHOW A BLANK HERE, ELSE DO THE SUMIF ON THE VALUE IN A2 AGAINST THE ENTIRE COLUMN A SUMMING VALUES IN COLUMN B.
    Hello JBeaucaire,

    You helped me sometime back with this.

    I just noticed something and not sure why it is doing this. I have attached the sheetPickList.xlsx

    On Sheet2 row 75 and row 80 it is totaling all quantities from sheet 1 and place it in those cells and the way you set this up it should only be putting a quantity in that cell if it is listed on sheet 1

    I do not see what is making it do this in the formula

  8. #8
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Summarizing data

    I think Excel is converting that leading ">" and "<" into working operators, so it's now reading "Greater than" & "1000 lbs Angle Braces".
    That's wild, I've never seen that before.

    Anyway, if you change the ">" to the words "More than" that should fix that problem.

  9. #9
    Forum Contributor
    Join Date
    03-16-2013
    Location
    Florida
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    132

    Re: Summarizing data

    O wow that did it thanks so much!!!

+ 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. Need some help with summarizing data...
    By vkeller in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-16-2012, 11:56 AM
  2. Summarizing data
    By Just James in forum Excel General
    Replies: 2
    Last Post: 04-27-2011, 07:54 AM
  3. Summarizing data
    By dallymo in forum Excel General
    Replies: 7
    Last Post: 01-22-2010, 11:38 AM
  4. Summarizing Data
    By jfixsen in forum Excel General
    Replies: 1
    Last Post: 10-06-2008, 02:17 PM
  5. [SOLVED] Need help with summarizing data
    By Cathy in forum Excel General
    Replies: 3
    Last Post: 10-20-2005, 04:05 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