SUM without prefix

[Boris]

Hi,

Could someone teach me how to add numbers without prefix column.

A
1 @101
2 102
3 @103
4 104
-----------
6 106

I need a function that could add 102 and 104 and result 106. I tried with
SUMPRODUCT and it doesn't seem to work. Prefix "@" was setup in column A1
and A3 to atuo input. Please help out.

Thanks,
Boris

 

[Ron Rosenfeld]

Hi,

Could someone teach me how to add numbers without prefix column.

A
1 @101
2 102
3 @103
4 104
-----------
6 106

I need a function that could add 102 and 104 and result 106. I tried with
SUMPRODUCT and it doesn't seem to work. Prefix "@" was setup in column A1
and A3 to atuo input. Please help out.

Thanks,
Boris

I can't help you with adding 102 and 104 to get 106. Possibly you mean 206?

If I understand you correctly, your cells contain:

A1: @101
A2: 102
A3: @103
A4: 104

In any event, if the values above are in Column A, and if the @ is present also
(not as a format, but as the actual character), then you can use the SUM
function

=SUM(A1:A4)

The values with the preceding @ should be being interpreted as TEXT, which the
SUM function will ignore.


--ron

 

[Boris]

Thanks Ron,

206 is the right amount I wanted.

The problem I'm having is "@" is used as a format and is input
automatically. Another word, I would only type 101 for A1 and 103 for A3.

Please help out anyone.

Thanks,
Boris

 

[Ron Rosenfeld]

Thanks Ron,

206 is the right amount I wanted.

The problem I'm having is "@" is used as a format and is input
automatically. Another word, I would only type 101 for A1 and 103 for A3.

Please help out anyone.

Thanks,
Boris

If I understand you correctly, the values in A1 and A3 are entered as 101 and
103, but display as @101 and @103.

1. Please post the actual cell format strings for A1 and A2.

2. How and when is the formatting of these cells created?

If the only difference between A1 and A2 is the formatting, we likely will need
a VBA solution.
--ron