count function
 

in cell a20 i want to count the range a1:a19 when there is data in the cell.
I am using the =count(a1:19) and it returns 19 everytime regardless if the
cell has data in or not. Cells a1:a19 do have a formula that indicates to
return the contents of another cell. Realizing this I tried to do a len
formula and I wrote it like this:
=count(len1,a1:a19). I then tried a whole host of other combinations of
different formulas but of course none of them worked simply because I do not
really know how to put a formula together. Can someone help me with this
formula?
Thank You
Brian
PS, barring computer courses in excel, where can I get comprehensive
information on how to create and put formulas together. I want to know what
things like the * does or why placement of Parenthesis are important, what is
the -- that I see alot. Excel does describe each formula but nowhere does it
explain the little details I mentioned above?

Try COUNTA()

"Brian" wrote in message
...
in cell a20 i want to count the range a1:a19 when there is data in the
cell.
I am using the =count(a1:19) and it returns 19 everytime regardless if the
cell has data in or not. Cells a1:a19 do have a formula that indicates to
return the contents of another cell. Realizing this I tried to do a len
formula and I wrote it like this:
=count(len1,a1:a19). I then tried a whole host of other combinations of
different formulas but of course none of them worked simply because I do
not
really know how to put a formula together. Can someone help me with this
formula?
Thank You
Brian
PS, barring computer courses in excel, where can I get comprehensive
information on how to create and put formulas together. I want to know
what
things like the * does or why placement of Parenthesis are important, what
is
the -- that I see alot. Excel does describe each formula but nowhere does
it
explain the little details I mentioned above?

Hi
try
=SUMPRODUCT(--(A1:A19<""))

--
Regards
Frank Kabel
Frankfurt, Germany
"Brian" schrieb im Newsbeitrag
...
in cell a20 i want to count the range a1:a19 when there is data in the
cell.
I am using the =count(a1:19) and it returns 19 everytime regardless if the
cell has data in or not. Cells a1:a19 do have a formula that indicates to
return the contents of another cell. Realizing this I tried to do a len
formula and I wrote it like this:
=count(len1,a1:a19). I then tried a whole host of other combinations of
different formulas but of course none of them worked simply because I do
not
really know how to put a formula together. Can someone help me with this
formula?
Thank You
Brian
PS, barring computer courses in excel, where can I get comprehensive
information on how to create and put formulas together. I want to know
what
things like the * does or why placement of Parenthesis are important, what
is
the -- that I see alot. Excel does describe each formula but nowhere does
it
explain the little details I mentioned above?

Use the Counta function

I found this by typing count in the help search bar.

Try these sites
http://www.cpearson.com/excel/topic.htm
http://www.mrexcel.com/board2/
http://office.microsoft.com/en-us/as...e/default.aspx

Good hunting

SKip Bisconer

"Brian" wrote:

in cell a20 i want to count the range a1:a19 when there is data in the cell.
I am using the =count(a1:19) and it returns 19 everytime regardless if the
cell has data in or not. Cells a1:a19 do have a formula that indicates to
return the contents of another cell. Realizing this I tried to do a len
formula and I wrote it like this:
=count(len1,a1:a19). I then tried a whole host of other combinations of
different formulas but of course none of them worked simply because I do not
really know how to put a formula together. Can someone help me with this
formula?
Thank You
Brian
PS, barring computer courses in excel, where can I get comprehensive
information on how to create and put formulas together. I want to know what
things like the * does or why placement of Parenthesis are important, what is
the -- that I see alot. Excel does describe each formula but nowhere does it
explain the little details I mentioned above?

One way

=COUNTA(A1:A19)-COUNTBLANK(A1:A19)

assuming that your blank result of the formula is "" and not " "

Regards

Peo Sjoblom

"Brian" wrote:

in cell a20 i want to count the range a1:a19 when there is data in the cell.
I am using the =count(a1:19) and it returns 19 everytime regardless if the
cell has data in or not. Cells a1:a19 do have a formula that indicates to
return the contents of another cell. Realizing this I tried to do a len
formula and I wrote it like this:
=count(len1,a1:a19). I then tried a whole host of other combinations of
different formulas but of course none of them worked simply because I do not
really know how to put a formula together. Can someone help me with this
formula?
Thank You
Brian
PS, barring computer courses in excel, where can I get comprehensive
information on how to create and put formulas together. I want to know what
things like the * does or why placement of Parenthesis are important, what is
the -- that I see alot. Excel does describe each formula but nowhere does it
explain the little details I mentioned above?

Hi
Thank You.
Could you translate this formula in regular talk so that I can understand
how it works?
What is this syntax saying if it can be said in words? What is the -- and <
"" saying and what is the improtance of the placement of the ()?
Thanks.

=SUMPRODUCT(--(A1:A19<""))

"Frank Kabel" wrote:

Hi
try


--
Regards
Frank Kabel
Frankfurt, Germany
"Brian" schrieb im Newsbeitrag
...
in cell a20 i want to count the range a1:a19 when there is data in the
cell.
I am using the =count(a1:19) and it returns 19 everytime regardless if the
cell has data in or not. Cells a1:a19 do have a formula that indicates to
return the contents of another cell. Realizing this I tried to do a len
formula and I wrote it like this:
=count(len1,a1:a19). I then tried a whole host of other combinations of
different formulas but of course none of them worked simply because I do
not
really know how to put a formula together. Can someone help me with this
formula?
Thank You
Brian
PS, barring computer courses in excel, where can I get comprehensive
information on how to create and put formulas together. I want to know
what
things like the * does or why placement of Parenthesis are important, what
is
the -- that I see alot. Excel does describe each formula but nowhere does
it
explain the little details I mentioned above?

The formula tests each cell in A1:A19 to not equal (<) to an
empty string (""). This results in an array of values, each
either TRUE or FALSE, each element the result of the respective
comparison operation. For example, the array might be {TRUE,
FALSE, ..., TRUE}. The double negative (--) forces the TRUE
values to a numeric value of 1 and the FALSE values to a numeric
value of 0. Finally, SUMPRODUCT adds up the 1s and 0s.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Brian" wrote in message
...
Hi
Thank You.
Could you translate this formula in regular talk so that I can
understand
how it works?
What is this syntax saying if it can be said in words? What is
the -- and <
"" saying and what is the improtance of the placement of the
()?
Thanks.

=SUMPRODUCT(--(A1:A19<""))

"Frank Kabel" wrote:

Hi
try


--
Regards
Frank Kabel
Frankfurt, Germany
"Brian" schrieb im
Newsbeitrag
...
in cell a20 i want to count the range a1:a19 when there is
data in the
cell.
I am using the =count(a1:19) and it returns 19 everytime
regardless if the
cell has data in or not. Cells a1:a19 do have a formula
that indicates to
return the contents of another cell. Realizing this I tried
to do a len
formula and I wrote it like this:
=count(len1,a1:a19). I then tried a whole host of other
combinations of
different formulas but of course none of them worked simply
because I do
not
really know how to put a formula together. Can someone help
me with this
formula?
Thank You
Brian
PS, barring computer courses in excel, where can I get
comprehensive
information on how to create and put formulas together. I
want to know
what
things like the * does or why placement of Parenthesis are
important, what
is
the -- that I see alot. Excel does describe each formula but
nowhere does
it
explain the little details I mentioned above?

Thank You,
=SUMPRODUCT(--(A1:A19<""))

I tired this formula but it still counts the blank cells for a total of 19,
I am looking to only count the cells that returned data. A1:A19 has the
relative formula =b1. so if nothing is returned in one of the A cells, that
cell should not be counted

Can you help me?
Thank you,
Brian

"Chip Pearson" wrote:

The formula tests each cell in A1:A19 to not equal (<) to an
empty string (""). This results in an array of values, each
either TRUE or FALSE, each element the result of the respective
comparison operation. For example, the array might be {TRUE,
FALSE, ..., TRUE}. The double negative (--) forces the TRUE
values to a numeric value of 1 and the FALSE values to a numeric
value of 0. Finally, SUMPRODUCT adds up the 1s and 0s.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Brian" wrote in message
...
Hi
Thank You.
Could you translate this formula in regular talk so that I can
understand
how it works?
What is this syntax saying if it can be said in words? What is
the -- and <
"" saying and what is the improtance of the placement of the
()?
Thanks.

=SUMPRODUCT(--(A1:A19<""))

"Frank Kabel" wrote:

Hi
try


--
Regards
Frank Kabel
Frankfurt, Germany
"Brian" schrieb im
Newsbeitrag
...
in cell a20 i want to count the range a1:a19 when there is
data in the
cell.
I am using the =count(a1:19) and it returns 19 everytime
regardless if the
cell has data in or not. Cells a1:a19 do have a formula
that indicates to
return the contents of another cell. Realizing this I tried
to do a len
formula and I wrote it like this:
=count(len1,a1:a19). I then tried a whole host of other
combinations of
different formulas but of course none of them worked simply
because I do
not
really know how to put a formula together. Can someone help
me with this
formula?
Thank You
Brian
PS, barring computer courses in excel, where can I get
comprehensive
information on how to create and put formulas together. I
want to know
what
things like the * does or why placement of Parenthesis are
important, what
is
the -- that I see alot. Excel does describe each formula but
nowhere does
it
explain the little details I mentioned above?

Hi
but in this case I'd guess you see a zero in these cells. Change your
formula to
=IF(B1="","",B1)
and the SUMPRODUCT formula should work

--
Regards
Frank Kabel
Frankfurt, Germany
"Brian" schrieb im Newsbeitrag
...
Thank You,
=SUMPRODUCT(--(A1:A19<""))

I tired this formula but it still counts the blank cells for a total of
19,
I am looking to only count the cells that returned data. A1:A19 has the
relative formula =b1. so if nothing is returned in one of the A cells,
that
cell should not be counted

Can you help me?
Thank you,
Brian

"Chip Pearson" wrote:

The formula tests each cell in A1:A19 to not equal (<) to an
empty string (""). This results in an array of values, each
either TRUE or FALSE, each element the result of the respective
comparison operation. For example, the array might be {TRUE,
FALSE, ..., TRUE}. The double negative (--) forces the TRUE
values to a numeric value of 1 and the FALSE values to a numeric
value of 0. Finally, SUMPRODUCT adds up the 1s and 0s.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Brian" wrote in message
...
Hi
Thank You.
Could you translate this formula in regular talk so that I can
understand
how it works?
What is this syntax saying if it can be said in words? What is
the -- and <
"" saying and what is the improtance of the placement of the
()?
Thanks.

=SUMPRODUCT(--(A1:A19<""))

"Frank Kabel" wrote:

Hi
try


--
Regards
Frank Kabel
Frankfurt, Germany
"Brian" schrieb im
Newsbeitrag
...
in cell a20 i want to count the range a1:a19 when there is
data in the
cell.
I am using the =count(a1:19) and it returns 19 everytime
regardless if the
cell has data in or not. Cells a1:a19 do have a formula
that indicates to
return the contents of another cell. Realizing this I tried
to do a len
formula and I wrote it like this:
=count(len1,a1:a19). I then tried a whole host of other
combinations of
different formulas but of course none of them worked simply
because I do
not
really know how to put a formula together. Can someone help
me with this
formula?
Thank You
Brian
PS, barring computer courses in excel, where can I get
comprehensive
information on how to create and put formulas together. I
want to know
what
things like the * does or why placement of Parenthesis are
important, what
is
the -- that I see alot. Excel does describe each formula but
nowhere does
it
explain the little details I mentioned above?