Add columns Total in Excel 2

[ZoomDah]

Hello everyone !
I work with Excel Spreadsheet and I've just created Report based on SQL query. It has like 20 columns most of which I want to see Total for.

How do I do this totaling stuff ?
What if columns will get shorter or longer after refresh ?

I appreciate your help.

 

[PHV]

Take a look in the properties of your QueryTable for something like Formula in adjacent cells.

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

 

[ZoomDah]

Thanks,
I am still looking ...
What if columns will get shorter or longer after refresh ?

 

[ZoomDah]

I am still searching for the info but no luck

Is it really that unusuall to want totals for the columns ?
Thanks

 

[SkipVought]

Zoom,

I would suggest using the SUBTOTAL spreadsheet function.
[tt]
=SUBTOTAL(9,C:C)
[/tt]


Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[ZoomDah]

I am ready to cry...
my column C let say:

1
0
3
7
5
=SUBTOTAL(9,C:C)

When I am trying to see results it is giving me "Circular References" error.

Thanks

 

[SkipVought]

Why are you putting the expression below the table? Totals at the BOTTOM of pages & tables are vestages of paper,pencil & adding machine!

1. Next time you refreshyour query, you could have gazillion more rows
2. Why force a user to search for the end of the table to find this important nugget of summary data?

Why not PROUDLY insert the formula at the top (by top it could either be in the same column ABOVE the table, in which case the formula needs to be changed in order to avoid a circular reference =SUBTOTAL(9,C2:C65536) OR it could mean off to the right of the table)

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[ZoomDah]

Skip, I would laugh - but can't ((

It must be bad day for me
part of the formula C2:C65536 gets blue and totals column to 0.

I have headers then blank row inserted and then query table.
I placed that formula in the cell above actual column I want toal for - "circular references" again.
I clicked Cancell (means ignore I guess) - total 0.
Should be 479.

(((

 

[SkipVought]

So you're putting the formula in C1?
Headings in Row 2?
Resultset beginning in Row 3?

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[ZoomDah]

Headers in row1
Blank row in row2
Query row3

Thanks

 

[ZoomDah]

TUNIT01 TUNIT02 TUNIT03

0 46 35
106 0 25
0 0 0

thia is what it looks like

 

[SkipVought]

=SUBTOTAL(9,C2:C65536) in C1, works fine for me!

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[ZoomDah]

Skip,
just entered in A
1
2
3
4
5
=SUBTOTAL(9,A2:A65536)

getting 0 with circular references

Could WK be corrupted or something checked that shouldn't be ?

It is too freaky.
Thanks for bearing with me.

 

[SkipVought]

Instead, in Column A enter
[tt]
=SUBTOTAL(9,A2:A65536)
1
2
3
4
5
[/tt]

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[ZoomDah]

Sometimes you (me in this case) just don't get it...

It was that I was entering formula into cell I am referencing in formula which action was creating circular references. I know it is NOT funny.

Thanks, Skip and have a star ( you deserve more then one for puting up with me yesterday )

Have a great day !

 

[ZoomDah]

One more, sorry.
What if data is not a Numeric - is there any way to convert it in formula and make Excel to count ?

 

[SkipVought]

If you check HELP
[tt]
SUBTOTAL
See Also

Returns a subtotal in a list or database. It is generally easier to create a list with subtotals using the Subtotals command (Data menu). Once the subtotal list is created, you can modify it by editing the SUBTOTAL function.

Syntax

SUBTOTAL(function_num,ref1,ref2,...)

Function_num is the number 1 to 11 that specifies which function to use in calculating subtotals within a list.

Function_Num Function
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP
[/tt]
So you could use =SUBTOTAL(3,C:C) for counting non-numeric

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[ZoomDah]

All the numbers are Numeric Long Integer. Those are the SAME practically like Item1 - Item20.
Works not for all.

Is there some way to refresh Excel file to make it to recognize formula or ...

It is so darn unbeliavable.

Thanks

 

[SkipVought]

What do you mean. Please explain in detail what does NOT work!

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[ZoomDah]

156 156 156 0.0% 17,128

103 884 127 696.1% 1,234
121 461 0 0.0% 893
0 636 0 0.0% 847
0 224 28 800.0% 551
120 230 0 0.0% 496
98 305 0 0.0% 486
0 172 103 167.0% 447
0 446 0 0.0% 446

See, the last column calculated right.
The last column uses 9 though...
---------------------------------------------
The rest of columns are giving me 156 (and I am remembered to change letter in formula)
SUBTOTAL(3,R3:R65536)
SUBTOTAL(3,S3:S65536)
SUBTOTAL(3,T3:T65536)..........

When I use 9 in the rest - gives me ##


All ARE Long Integers, checked for sure.