Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Only Empty Cells AFTER Cells with Data
I have a constant range of cells D8:D31 (two periods per month) on each sheet
where I need to get the number of cells with data, and the number of blank cells AFTER cells with data. (I don't want to include the empty cells before the sales data starts). I need to do a projection of sales for the balance of the year and need number of months with data and the REMAINING months without data. I have over 100 sheets, one for each location, and would rather not customize the formula for each sheet as new locations are added. Is there a formula (can't see how) or maybe a macro that would give me these two numbers? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Only Empty Cells AFTER Cells with Data
=COUNTA(D8:D31)
and =24-previous_formula or =COUNTBLANK(D8:D31) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "David" wrote in message ... I have a constant range of cells D8:D31 (two periods per month) on each sheet where I need to get the number of cells with data, and the number of blank cells AFTER cells with data. (I don't want to include the empty cells before the sales data starts). I need to do a projection of sales for the balance of the year and need number of months with data and the REMAINING months without data. I have over 100 sheets, one for each location, and would rather not customize the formula for each sheet as new locations are added. Is there a formula (can't see how) or maybe a macro that would give me these two numbers? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Only Empty Cells AFTER Cells with Data
Thanks Bob,
That will include empty spaces BEFORE data that may start in March for example. I need something that will recognize the first cell with data, then begin the count, and then count only empty cells to the end of the range after the data. In this example, I would not want the cells for January and February included. "Bob Phillips" wrote: =COUNTA(D8:D31) and =24-previous_formula or =COUNTBLANK(D8:D31) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "David" wrote in message ... I have a constant range of cells D8:D31 (two periods per month) on each sheet where I need to get the number of cells with data, and the number of blank cells AFTER cells with data. (I don't want to include the empty cells before the sales data starts). I need to do a projection of sales for the balance of the year and need number of months with data and the REMAINING months without data. I have over 100 sheets, one for each location, and would rather not customize the formula for each sheet as new locations are added. Is there a formula (can't see how) or maybe a macro that would give me these two numbers? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro question | Excel Worksheet Functions | |||
HOW DO I COUNT CELLS WITH DATA BY COLUMN? | Excel Worksheet Functions | |||
Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
Is there an easy way to count when adjacent cells contain same data? | Excel Worksheet Functions | |||
Convert data type of cells to Text,Number,Date and Time | Excel Worksheet Functions |