How to count cells that do not contain "-"

's Computer Specifications

I have a row of data where each cell is using a VLOOKUP formula - the formula
either returns a number or "-". I need to count all the cells that contain a
NUMBER only and I don't want to count the cells that contain "-". I have
tried COUNTIF, SUMPRODUCT...

Please help

Example

A B C D
1 3 - 4 I want formula in this cell to return the
value of 2 (because there are numbers in cell A1 and C1, while B1 contains
"-" which I don't want to count.

's Computer Specifications

Try

Countif($A$1:$D$1,">0")

"jcannon1" wrote:

> I have a row of data where each cell is using a VLOOKUP formula - the formula
> either returns a number or "-". I need to count all the cells that contain a
> NUMBER only and I don't want to count the cells that contain "-". I have
> tried COUNTIF, SUMPRODUCT...
>
> Please help
>
> Example
>
> A B C D
> 1 3 - 4 I want formula in this cell to return the
> value of 2 (because there are numbers in cell A1 and C1, while B1 contains
> "-" which I don't want to count.
>

's Computer Specifications

Did you try this sumproduct formula

=SUMPRODUCT(--(ISNUMBER(A8:E8)))

Mike

"jcannon1" wrote:

> I have a row of data where each cell is using a VLOOKUP formula - the formula
> either returns a number or "-". I need to count all the cells that contain a
> NUMBER only and I don't want to count the cells that contain "-". I have
> tried COUNTIF, SUMPRODUCT...
>
> Please help
>
> Example
>
> A B C D
> 1 3 - 4 I want formula in this cell to return the
> value of 2 (because there are numbers in cell A1 and C1, while B1 contains
> "-" which I don't want to count.
>

's Computer Specifications

If the choices for each cell are a number of a dash, then you can use:
=count(a1:c1)
to count the numbers

If you could have anything in those cells, you could use:
=3-countif(a1:c1,"-")
(3 is the number of cells in the range you're checking.)

jcannon1 wrote:
>
> I have a row of data where each cell is using a VLOOKUP formula - the formula
> either returns a number or "-". I need to count all the cells that contain a
> NUMBER only and I don't want to count the cells that contain "-". I have
> tried COUNTIF, SUMPRODUCT...
>
> Please help
>
> Example
>
> A B C D
> 1 3 - 4 I want formula in this cell to return the
> value of 2 (because there are numbers in cell A1 and C1, while B1 contains
> "-" which I don't want to count.

--

Dave Peterson

's Computer Specifications

ps. I'm assuming that you really have a dash in the cell. Not a 0 with a
special format.

Dave Peterson wrote:
>
> If the choices for each cell are a number of a dash, then you can use:
> =count(a1:c1)
> to count the numbers
>
> If you could have anything in those cells, you could use:
> =3-countif(a1:c1,"-")
> (3 is the number of cells in the range you're checking.)
>
> jcannon1 wrote:
> >
> > I have a row of data where each cell is using a VLOOKUP formula - the formula
> > either returns a number or "-". I need to count all the cells that contain a
> > NUMBER only and I don't want to count the cells that contain "-". I have
> > tried COUNTIF, SUMPRODUCT...
> >
> > Please help
> >
> > Example
> >
> > A B C D
> > 1 3 - 4 I want formula in this cell to return the
> > value of 2 (because there are numbers in cell A1 and C1, while B1 contains
> > "-" which I don't want to count.
>
> --
>
> Dave Peterson

--

Dave Peterson

's Computer Specifications

jcannon1 wrote:
> I have a row of data where each cell is using a VLOOKUP formula - the formula
> either returns a number or "-". I need to count all the cells that contain a
> NUMBER only and I don't want to count the cells that contain "-". I have
> tried COUNTIF, SUMPRODUCT...
>
> Please help
>
> Example
>
> A B C D
> 1 3 - 4 I want formula in this cell to return the
> value of 2 (because there are numbers in cell A1 and C1, while B1 contains
> "-" which I don't want to count.
>
=COUNTIF(A1:C1,"<>-")

Alan Beban

's Computer Specifications

Thanks... It worked perfectly! I was having a brain fart because this is a
pretty easy formula.

"akphidelt" wrote:

> Try
>
> Countif($A$1:$D$1,">0")
>
> "jcannon1" wrote:
>
> > I have a row of data where each cell is using a VLOOKUP formula - the formula
> > either returns a number or "-". I need to count all the cells that contain a
> > NUMBER only and I don't want to count the cells that contain "-". I have
> > tried COUNTIF, SUMPRODUCT...
> >
> > Please help
> >
> > Example
> >
> > A B C D
> > 1 3 - 4 I want formula in this cell to return the
> > value of 2 (because there are numbers in cell A1 and C1, while B1 contains
> > "-" which I don't want to count.
> >

's Computer Specifications

Thanks Mike! This worked perfectly. Can you tell me what the 2 dashes ("--")
after the sumproduct signify?

"Mike H" wrote:

> Did you try this sumproduct formula
>
> =SUMPRODUCT(--(ISNUMBER(A8:E8)))
>
> Mike
>
> "jcannon1" wrote:
>
> > I have a row of data where each cell is using a VLOOKUP formula - the formula
> > either returns a number or "-". I need to count all the cells that contain a
> > NUMBER only and I don't want to count the cells that contain "-". I have
> > tried COUNTIF, SUMPRODUCT...
> >
> > Please help
> >
> > Example
> >
> > A B C D
> > 1 3 - 4 I want formula in this cell to return the
> > value of 2 (because there are numbers in cell A1 and C1, while B1 contains
> > "-" which I don't want to count.
> >

's Computer Specifications

See http://xldynamic.com/source/xld.SUMPRODUCT.html

--
__________________________________
HTH

Bob

"jcannon1" <(E-Mail Removed)> wrote in message
news:767FCC28-B08C-4CAA-BA3A-(E-Mail Removed)...
> Thanks Mike! This worked perfectly. Can you tell me what the 2 dashes
> ("--")
> after the sumproduct signify?
>
> "Mike H" wrote:
>
>> Did you try this sumproduct formula
>>
>> =SUMPRODUCT(--(ISNUMBER(A8:E8)))
>>
>> Mike
>>
>> "jcannon1" wrote:
>>
>> > I have a row of data where each cell is using a VLOOKUP formula - the
>> > formula
>> > either returns a number or "-". I need to count all the cells that
>> > contain a
>> > NUMBER only and I don't want to count the cells that contain "-". I
>> > have
>> > tried COUNTIF, SUMPRODUCT...
>> >
>> > Please help
>> >
>> > Example
>> >
>> > A B C D
>> > 1 3 - 4 I want formula in this cell to return
>> > the
>> > value of 2 (because there are numbers in cell A1 and C1, while B1
>> > contains
>> > "-" which I don't want to count.
>> >

Thread Tools
Show Printable Version Email this Page
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to add a "running count" to cells with matching data	WA	Microsoft Excel Programming	2	7th Jun 2010 02:45 PM
count cells in a document, but excude cells with a "0"	R.M.	Microsoft Excel Misc	1	26th Apr 2010 10:19 PM
Count the number of cells that conatin a "." in a range	Jonathan Brown	Microsoft Excel Worksheet Functions	5	5th Feb 2009 11:00 PM
Count cells that contain "Y" in columnA IF contains"X" in columnB	=?Utf-8?B?aG9sbGllZGF2aXM=?=	Microsoft Excel Worksheet Functions	6	20th Jul 2006 06:12 PM
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next	BCB	Microsoft Excel New Users	7	13th May 2006 10:02 PM