# Summarizing data

1. ## 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. ## 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.

3. ## 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. ## 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. ## 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. ## 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. ## Re: Summarizing data

Originally Posted by JBeaucaire
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. ## 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. ## Re: Summarizing data

O wow that did it thanks so much!!!

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

#### 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