Summarizing data

**icevinson** · 11-17-2014, 09:55 PM

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

**JBeaucaire** · 11-18-2014, 12:13 AM

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.

**icevinson** · 11-18-2014, 02:56 PM

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

**JBeaucaire** · 11-18-2014, 09:23 PM

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.

**icevinson** · 11-18-2014, 10:02 PM

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.

**JBeaucaire** · 11-19-2014, 04:15 PM

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.

**icevinson** · 02-19-2015, 04:25 PM

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

**JBeaucaire** · 02-19-2015, 08:01 PM

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.

**icevinson** · 02-19-2015, 08:47 PM

O wow that did it thanks so much!!!

Summarizing data

LinkBack

Thread Tools

Rate This Thread

Display