how do I sum up column values

[Frank]

Hi All,

I am an accountant and start to use excel. I have the following question:

Say in an Excel worksheet, I have to columns as the following:

Account# Value
101 25.37
101 (12.2)
103 22l5
101 -
102 11.23
101 (10.2)
......

I would like to get the sum for account number 101, how can I do that?
Assume all the numbers in bracket is nagative and - for 0.

Thanks very much in advance.

Frank

 

[JulieD]

Hi Frank

there's a couple of ways:
1) use the SUMIF function
=SUMIF(A2:A100,101,B2:B100)
where the Account numbers are in A2:A100 & the values are in B2:B100. If
the account numbers are text instead of "numbers" use
=SUMIF(A2:A100,"101",B2:B100)

2) sort by Account# (Data / Sort) and use the Subtotal feature (Data /
Subtotals)
- At each change in Account Number, Sum Value

3) use a pivot table (Data / Pivot Table & Pivot Chart Report)
click in your data - choose Data / Pivot Table & Pivot Chart Report
click next until the wizard is finished - most of the answers should be the
default ones
drag the account number to the "row" section
drag the value to the "data" section

Hope this helps
Cheers
JulieD

 

[Frank]

Hi Julie,

Thanks for your quick reply. But will the sum function "automatically"
recognize (23.45) as -23.45, and - as 0.0? Also, how do I tell the function
that something is a number but not a text or vice versa?

Thanks again.

Frank

 

[JulieD]

Hi Frank

generally when typing a negative number in you type
-24.57
(or whatever) and then the display of that number
(24.57)
is controlled by the cell's format
format / cell / number ... there's lots of ways of formatting negatives &
zeros

if you have entered
(24.57)
the Excel (on my version) anyway treats it as a negative number and not text
but to check whether Excel thinks you have numbers or text use the following
function in a cell next to one of your negatives
=ISNUMBER(B7)
where B7 is the cell reference of your negative number
if TRUE is returned then you're fine - in FALSE then Excel thinks it's text.

Let me know if we're playing with text or numbers - as if you have "text" i
think it would be best to convert them to numbers and we can then discuss
the methods of doing this. If it is numbers, positive, negative or zero
Excel will calculate them correctly.

Cheers
JulieD