Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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? |
#6
|
|||
|
|||
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? |
#7
|
|||
|
|||
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? |
#8
|
|||
|
|||
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? |
#9
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Whats the function to count the total times a word is displayed | Excel Discussion (Misc queries) | |||
count function | Excel Worksheet Functions | |||
How can I use count function in excel where I have several criter. | Excel Worksheet Functions | |||
Sum and Count Function | Excel Worksheet Functions | |||
how do I make a function to count days? | Excel Worksheet Functions |