Need help totaling

[PeteAmsterdam]

HI I need to provide totals based on the color column:
THe color column is ALB, ANA, ARK. When there is a 'break' a new color I want to include a total line of the previous color.

LA64592 ALB MN PGSP VARSITY JK 8/09/04* 18 3
LA74482 ALB MN PVNC RAINWEAR 7/09/04 356 356
LA78532 ALB MN PVNC VARSITY JK 7/25/04 18 3
LA25785 ANA MN MCFB ZIP FRT JK 3/29/04 12 2
LA58342 ARK MN PGNP VARSITY JK 3/29/04 35 6
LA90292 ARK MN CTTN SWEATSHIRT 3/29/04 71 1 2

I am unsure of how to code this in Excel.

TIA,
Pete

 

[SkipVought]

Pete,

No code needed. Just use the Data/Subtotals wizard to aggregate.

Skip,

_{[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie!}

 

[PeteAmsterdam]

THanks Skip but i get 0. For each change in color, i want to sum Qty.

 

[SkipVought]

Did you specify the COLUMN to aggregate?

Skip,

_{[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie!}

 

[PeteAmsterdam]

No but I don't see where to do this.

 

[SkipVought]

For each change in...

Color

use function...

Sum

Add subtotal to...

[THE HEADING NAME OF YOUR COLUMN]

Skip,

_{[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie!}

 

[PeteAmsterdam]

Yep but give a zero

V LA05332 ALB MN NYLN POP OVER JK 8/14/05 18
V LA62972 ALB MN PGSP ZIP FRT JK 8/08/05 24
V LA71582 ALB MN PVNC VARSITY JK 9/08/05 48
V LA74482 ALB MN PVNC RAINWEAR 7/19/05 122
V LA81422 ALB MN WOOL VARSITY JK 10/01/05 24
V LA88592 ALB MN WLPN VARSITY JK 10/01/05 7
V LB71582 ALB BY PVNC VARSITY JK 9/21/05 30
V NM03662 ALB WM NYLN ZIP FRT JK 8/24/05 36
ALB Total 0

 

[PHV]

You must sum a numeric column.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PeteAmsterdam]

For the Grand total for example it creates this formula

=SUBTOTAL(9,F6:F1367)

why does this produce a zero?

 

[PeteAmsterdam]

Its defined NUMBER w/ no decimals.

 

[SkipVought]

I get 309 from your example

Skip,

_{[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie!}

 

[Gavona]

can you do a simple =Sum(F6:F1367) does that give a total? If not then you don't have numbers in the column.

To convert:
type 1 in a cel somewhere.
Copy that cell
Select column F
Edit,Pastespecial,multiply

If that doesn't work you may need to create a new column G and use a formula in G6 like =1*trim(F6)

Thanks,

Gavin

 

[PeteAmsterdam]

Always comes up zero. I made a new column, entered 4 lines of values and that one worked fine. But not the original one. It did come from another computer system originally but why should that matter?

 

[xlbo]

When you import data into excel, excel must decide what KIND of data that is. Sometimes it makes an incorrect decision and sees numeric data as text - that is what has happened here

To convert, follow Gavona's instructions regarding Paste Special>Values/Multiply

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[PeteAmsterdam]

Is it possible that the problem is that I have headings in that column as well and they get in the way?

 

[SkipVought]

Headings are NOT the problem.

Pete, as a programmer, you ought to know that there is a HUGE difference between NUMBERS, used for arithmetic calculation and STRINGS of NUMERIC DIGITS. They are vastly different values and are handled by computers in different ways.

Applications like Excel, make it easy to FORGET that difference, since Excel often makes assumptions about data.

Format a column as TEXT. Then enter some numbers in the TEXT column. Then sum the "numbers". == ZERO! That's because they are STORED as STRING.

That is your problem, as has already been pointed out to you, along with a solution.

Skip,

_{[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie!}

 

[xlbo]

Easiest way to tell.......try the

=ISNUMBER()
and
=ISTEXT()

functions - they will tell you what kind of data you have

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question