The One Formula
 

I am using this formula =SUM(C4:C46) however I want to change it so that it
counts any number in its range such as the number 2, 3, 4, 5, etc as a 1,
including the actual number 1.

The One Formula
 

Can you provide a small sample of the data (about five rows), along with
the actual expected result?

http://www.xl-central.com

In article ,
Workbook wrote:

I am using this formula =SUM(C4:C46) however I want to change it so that it
counts any number in its range such as the number 2, 3, 4, 5, etc as a 1,
including the actual number 1.

The One Formula
 

This is an ARRAY formula that must be entered using ctrl + shift+enter

=COUNT(IF(ISNUMBER(c4:c46),c4:c46))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Workbook" wrote in message
...
I am using this formula =SUM(C4:C46) however I want to change it so that
it
counts any number in its range such as the number 2, 3, 4, 5, etc as a 1,
including the actual number 1.

The One Formula
 

Try

=SUMPRODUCT(--ISNUMBER(C4:C46))

Mike

"Workbook" wrote:

I am using this formula =SUM(C4:C46) however I want to change it so that it
counts any number in its range such as the number 2, 3, 4, 5, etc as a 1,
including the actual number 1.

The One Formula
 

Hmmm

=COUNT(C4:C46)

Mike

"Mike H" wrote:

Try

=SUMPRODUCT(--ISNUMBER(C4:C46))

Mike

"Workbook" wrote:

I am using this formula =SUM(C4:C46) however I want to change it so that it
counts any number in its range such as the number 2, 3, 4, 5, etc as a 1,
including the actual number 1.

The One Formula
 

Nice ;-)


"Mike H" wrote in message
...
Hmmm

=COUNT(C4:C46)

Mike

"Mike H" wrote:

Try

=SUMPRODUCT(--ISNUMBER(C4:C46))

Mike

"Workbook" wrote:

I am using this formula =SUM(C4:C46) however I want to change it so
that it
counts any number in its range such as the number 2, 3, 4, 5, etc as a
1,
including the actual number 1.

The One Formula
 

I think I made a mistake in my explanation. Thank you very much for your
help! Because I think it you interpret what I said differently this formula
does exactly what I asked. =COUNT(IF(ISNUMBER(c4:c46),c4:c46)) returns a 1

"Don Guillett" wrote:

This is an ARRAY formula that must be entered using ctrl + shift+enter

=COUNT(IF(ISNUMBER(c4:c46),c4:c46))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Workbook" wrote in message
...
I am using this formula =SUM(C4:C46) however I want to change it so that
it
counts any number in its range such as the number 2, 3, 4, 5, etc as a 1,
including the actual number 1.

The One Formula
 

Works like a charm! thanks for you're help!

"Mike H" wrote:

Try

=SUMPRODUCT(--ISNUMBER(C4:C46))

Mike

"Workbook" wrote:

I am using this formula =SUM(C4:C46) however I want to change it so that it
counts any number in its range such as the number 2, 3, 4, 5, etc as a 1,
including the actual number 1.

The One Formula
 

Even more simple and also works great! Thank you

"Mike H" wrote:

Hmmm

=COUNT(C4:C46)

Mike

"Mike H" wrote:

Try

=SUMPRODUCT(--ISNUMBER(C4:C46))

Mike

"Workbook" wrote:

I am using this formula =SUM(C4:C46) however I want to change it so that it
counts any number in its range such as the number 2, 3, 4, 5, etc as a 1,
including the actual number 1.