Sum the shown values of a cell

[Ali]

Hi there. I have a spreadsheet with values in D3:J3 for example
The values in each line are obtained by a multiplication formula of 2 other
cells. Example would be in D3 (=$D$2*C3) with D2 = 6.44 and C3 = 0.5
The format for D3:J3 is set so that the value of this mutiplication is
rounded off, which means that cell D3 would show 3 instead of 3.22

However i now need to SUM all these values but i need to Sum the Value SHOWN
in the cell and not the actual value. I need the 3 to add to the cell next
to it and not use the 3.22
Values shown in D3:J3 are:
3,4,3,3,1,1 so total needed to buy should be 15...

i know it should be simple but I have tried =Sum(Value(D3:J3)) but it is not
returning 15, but 14,04

Please can anyone assist,
Many thanks
ali

 

[Mike H]

Hi,

Try this array formula

=SUM(INT(D3:J3))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array.

Mike

 

[Ali]

Hi Mike
Sorry Gremlins in system as this was posted into another question.
If I set the Format of the cells to be 'Number" with 2 decimals values are:
3.22 / 3.61 / 2.74 / 2.78 / 1.00 / 0.79 Sums = 14.14

If I set the Format to Custom and use _(*#,##0);(#,##0);(*"-"?? etc
values become:

3 / 4 / 3 / 3 / 1 / 1 ( I would like Sum value to reflect 15)
If i use your array formula of =Sum(INT(D3:J3)) i get a value of 11????
if I use = Sum(ABS(D3:J3)) i get the value of 14.4
If I use =Sum(VALUE(D3:J3)) I get a value of 14

But I cannot seem to get a value of 15. thanks

 

[joeu2004]

If i use your array formula of =Sum(INT(D3:J3))
i get a value of 11?

When you use a numeric format, Excel __rounds__, not truncates, the
value to the number of decimal places specified (zero in your case).
Of course, that affects only the __displayed__ value, not the true
value of the cell.

You can use the following array formula (commit with ctrl-shift-
Enter):

=sum(round(D3:J3,0))

Or you can use the following normal formula (commit with Enter):

=sumproduct(round(D3:J3,0))

Alternatively, you can select the option to calculation option
"Precision as displayed" (click Tools > Options > Calculation). But I
wouldn't. It impacts calculations on all worksheets, which can lead
to some unintended results.

 

[Ali]

Hi Joe
Thanks the sumproduct is working fine. It now means that stores can
purchase exactly the total needed if you Sum the DISPLAYED values in the
individual cells.
Many thanks for the help.
Mike, thanks as well to you
ali