| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
