Add numbers in a column, but exclude percentages in the same colum
 

First of all, I did not set this spreadsheet up, but maybe I can learn
something . . .

I have a user who needs to add a column of numbers. The problem is that
there are also percentages in this same column. The percentages cannot be
added in.

Initially, I had him use the formula: =SUMIF(A1:A35,"=1"), ASSUMING that
any non-percentage was above 1. Unfortuantely, this is not the case.

Can I modify this formula or use another formula so that it can tell that a
cell is formatted as percentage?

Thanks in advance.

Rich

Add numbers in a column, but exclude percentages in the same colum
 

=IF(LEFT(CELL("format",A2))="P","formatted as percentage","not percentage")
--
David Biddulph

"Rich W." wrote in message
...
First of all, I did not set this spreadsheet up, but maybe I can learn
something . . .

I have a user who needs to add a column of numbers. The problem is that
there are also percentages in this same column. The percentages cannot be
added in.

Initially, I had him use the formula: =SUMIF(A1:A35,"=1"), ASSUMING that
any non-percentage was above 1. Unfortuantely, this is not the case.

Can I modify this formula or use another formula so that it can tell that
a
cell is formatted as percentage?

Thanks in advance.

Rich

Add numbers in a column, but exclude percentages in the same c
 

David,

This is a good thought j- I believe it's what I'm looking for - but I'm not
sure how to apply it. Initially, my formula was/is: =SUMIF(A1:A35,"=1").

So I thought, I'll just modify the formula to this:
=SUMIF(A1:A35,(LEFT(CELL("format")="P")))
(obviously, I dropped out the cell address for the CELL function)

I don't think I can really use a cell reference, like you did (i.e., the
"A2" after "format",), since I'm summing a range.

Am I misunderstanding something, or is there another way?

Thanks for any information . . .

Rich

"David Biddulph" wrote:

=IF(LEFT(CELL("format",A2))="P","formatted as percentage","not percentage")
--
David Biddulph

"Rich W." wrote in message
...
First of all, I did not set this spreadsheet up, but maybe I can learn
something . . .

I have a user who needs to add a column of numbers. The problem is that
there are also percentages in this same column. The percentages cannot be
added in.

Initially, I had him use the formula: =SUMIF(A1:A35,"=1"), ASSUMING that
any non-percentage was above 1. Unfortuantely, this is not the case.

Can I modify this formula or use another formula so that it can tell that
a
cell is formatted as percentage?

Thanks in advance.

Rich

Add numbers in a column, but exclude percentages in the same c
 

If you can't find another way, use a helper column.

You could use =IF(LEFT(CELL("format",A2))="P",A2,"") and SUM that column.
--
David Biddulph

"Rich W." wrote in message
...
David,

This is a good thought j- I believe it's what I'm looking for - but I'm
not
sure how to apply it. Initially, my formula was/is: =SUMIF(A1:A35,"=1").

So I thought, I'll just modify the formula to this:
=SUMIF(A1:A35,(LEFT(CELL("format")="P")))
(obviously, I dropped out the cell address for the CELL function)

I don't think I can really use a cell reference, like you did (i.e., the
"A2" after "format",), since I'm summing a range.

Am I misunderstanding something, or is there another way?

Thanks for any information . . .

Rich

"David Biddulph" wrote:

=IF(LEFT(CELL("format",A2))="P","formatted as percentage","not
percentage")
--
David Biddulph

"Rich W." wrote in message
...
First of all, I did not set this spreadsheet up, but maybe I can learn
something . . .

I have a user who needs to add a column of numbers. The problem is that
there are also percentages in this same column. The percentages cannot
be
added in.

Initially, I had him use the formula: =SUMIF(A1:A35,"=1"), ASSUMING
that
any non-percentage was above 1. Unfortuantely, this is not the case.

Can I modify this formula or use another formula so that it can tell
that
a
cell is formatted as percentage?

Thanks in advance.

Rich