Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count nonblank cells
I have a column B9:B100 on Sheet 1 in Excel 2007 in which each cell includes
a formula (=Sheet2!A9). The results of a search appear on Sheet2, then appear on Sheet1. (I had to do this cause I learned I could not use an array formula in cells that have been merged.) Anyway, the column on Sheet1 may contain numbers/text, and some appear blank because I replaced the 0s and #N/A with a blank. So, for example, after a search, there may be numbers/text that appear in B9:B15, and then B16:B100 are "blank" even though they still contain the formula copying text/numbers from Sheet2. I want to count how many nonblank cells there are to show how many matches were foind. None of the counting functions work (e.g., COUNT, COUNTA, COUNTBLANK, etc.) because I am assuming Excel still sees that a cell, although appearing blank, still contains a formula, so its not technically blank. So, if numbers/text that appear in B9:B15, and then B16:B100 are "blank", I tried =COUNTA(B9:B100), and get a count 92. Clearly there are not 92 nonblank cells. There are only 7. Any thoughts how to count the number of cells showing search results and NOT count the cells that appear blank and only contain the formula. Thanks!!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count nonblank cells
Through experimentation, figured it out...
=COUNTIF(B9:B100,"?*") "Art" wrote: I have a column B9:B100 on Sheet 1 in Excel 2007 in which each cell includes a formula (=Sheet2!A9). The results of a search appear on Sheet2, then appear on Sheet1. (I had to do this cause I learned I could not use an array formula in cells that have been merged.) Anyway, the column on Sheet1 may contain numbers/text, and some appear blank because I replaced the 0s and #N/A with a blank. So, for example, after a search, there may be numbers/text that appear in B9:B15, and then B16:B100 are "blank" even though they still contain the formula copying text/numbers from Sheet2. I want to count how many nonblank cells there are to show how many matches were foind. None of the counting functions work (e.g., COUNT, COUNTA, COUNTBLANK, etc.) because I am assuming Excel still sees that a cell, although appearing blank, still contains a formula, so its not technically blank. So, if numbers/text that appear in B9:B15, and then B16:B100 are "blank", I tried =COUNTA(B9:B100), and get a count 92. Clearly there are not 92 nonblank cells. There are only 7. Any thoughts how to count the number of cells showing search results and NOT count the cells that appear blank and only contain the formula. Thanks!!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count nonblank cells
When you say that the cells contain "numbers/text", what exactly does that
mean? Does it mean the cells contain *either* numbers or text. Or, does it mean the cells contain alphanumeric strings (which are considered text)? I want to count how many nonblank cells there are Try this... =COUNT(B9:B100)+COUNTIF(B9:B100,"?*") The COUNT will count any numeric numbers and the COUNTIF will count any text entries but *exclude* formula blanks. -- Biff Microsoft Excel MVP "Art" wrote in message ... I have a column B9:B100 on Sheet 1 in Excel 2007 in which each cell includes a formula (=Sheet2!A9). The results of a search appear on Sheet2, then appear on Sheet1. (I had to do this cause I learned I could not use an array formula in cells that have been merged.) Anyway, the column on Sheet1 may contain numbers/text, and some appear blank because I replaced the 0s and #N/A with a blank. So, for example, after a search, there may be numbers/text that appear in B9:B15, and then B16:B100 are "blank" even though they still contain the formula copying text/numbers from Sheet2. I want to count how many nonblank cells there are to show how many matches were foind. None of the counting functions work (e.g., COUNT, COUNTA, COUNTBLANK, etc.) because I am assuming Excel still sees that a cell, although appearing blank, still contains a formula, so its not technically blank. So, if numbers/text that appear in B9:B15, and then B16:B100 are "blank", I tried =COUNTA(B9:B100), and get a count 92. Clearly there are not 92 nonblank cells. There are only 7. Any thoughts how to count the number of cells showing search results and NOT count the cells that appear blank and only contain the formula. Thanks!!! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
count nonblank cells
Hi,
Why does the criteria include both the wildcards. Shouldn't the criteria be * only I.e. =countif(B9:B100,"*") -- Regards, Ashish Mathur Microsoft Excel MVP "Art" wrote in message ... Through experimentation, figured it out... =COUNTIF(B9:B100,"?*") "Art" wrote: I have a column B9:B100 on Sheet 1 in Excel 2007 in which each cell includes a formula (=Sheet2!A9). The results of a search appear on Sheet2, then appear on Sheet1. (I had to do this cause I learned I could not use an array formula in cells that have been merged.) Anyway, the column on Sheet1 may contain numbers/text, and some appear blank because I replaced the 0s and #N/A with a blank. So, for example, after a search, there may be numbers/text that appear in B9:B15, and then B16:B100 are "blank" even though they still contain the formula copying text/numbers from Sheet2. I want to count how many nonblank cells there are to show how many matches were foind. None of the counting functions work (e.g., COUNT, COUNTA, COUNTBLANK, etc.) because I am assuming Excel still sees that a cell, although appearing blank, still contains a formula, so its not technically blank. So, if numbers/text that appear in B9:B15, and then B16:B100 are "blank", I tried =COUNTA(B9:B100), and get a count 92. Clearly there are not 92 nonblank cells. There are only 7. Any thoughts how to count the number of cells showing search results and NOT count the cells that appear blank and only contain the formula. Thanks!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count nonblank cells with conditions | Excel Worksheet Functions | |||
Count nonblank cells based on another column | Excel Worksheet Functions | |||
count 2 nonblank cells on multiple worksheets | Excel Discussion (Misc queries) | |||
How do I count nonblank cells in rows within Excel? | New Users to Excel | |||
Count nonblank cells with multiple criteria | Excel Worksheet Functions |