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

[Rich W.]

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

 

[David Biddulph]

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

 

[Rich W.]

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]

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.