Sum up columns, but only cells holding hardcoded values 2

[firegambler]

Hi everyone,

do you have a hint for me, whether there is a non-macro solution to sum up the values of a column - but only those cells that contain a hardcoded value. Cells that contain a formula should not be summed up.

e.g.
a1: =4+2
a2: 2
a3: 3

a4: function(a1:a3) --> result = 5


Thanks a lot!

 

[SkipVought]

hi,

No. A value is a value. one would rarely see a formula as =4+2.

What is your business case for this requirement?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[firegambler]

Hi Skip,
thanks for your reply.

I have a document that has manually coded subtotals. I have doubts that the subtotals were calculated correctly and would like tho check the overall result.

Cheers

f.

 

[SkipVought]

Use the SUBTOTAL function to SUM or COUNT or AVERAGE, etc. Each type of aggregation has TWO function numbers. READ Excel Help in SUBTOTAL for the FULL scoop.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

IF your manually coded sub-totals use the subtotal function then the grand total will be unaffected by the sub-totals. However your example suggests that this is not the case, so:

If you have another column that can help to identify the sub-totals then you can use that to filter out these existing sub-total rows. (Use autofilter with, for example a custom criteria "does not contain "total" or to filter for blanks in one column but non-blanks in the column to be totalled).
The subtotal function applied to the entire column will show the total of the visible rows only and thus enable you to check the grand total ("overall result"). Equally the filtered total can be made to display in the statusbar.

OR (xl2003)

Select the column of data,
Edit,GoTo,Special,constants
view the total of selected cells on the right hand end of the Status Bar
(beware if some of your data is actually a formula like = 100+75 it will not be identified as a constant. (Hold down Ctrl to add to your selection manually.)

OR, if you are lucky with your data layout, the autosum function will create a good sum(...) formula.
Use goto,special to select formulae,
then click the autosum button.

Remember you can copy the column to play with without ruining the existing data. Once you have good formulae in this working column you can delete all the constants so you just have the formulae in the column and then Copy, PasteSpecial, SKIPBLANKS over the original.


Gavin

 

[firegambler]

Hi,
thanks for your replies!
I rebuilt the manual subtotals using the subtotal() formula.
That way I found all the errors.

cheers
f.

 

[lionelhill]

For future reference, the most well-hidden Excel key combination: Ctrl-` will show all formulae as formulae on the worksheet you are viewing. It's a quick way to see manually which cells contain somethign like =2+4 instead of just 6. Also available as tools - formula auditing.