Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,560
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,560
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro question Chris Excel Worksheet Functions 12 July 7th 06 01:23 AM
HOW DO I COUNT CELLS WITH DATA BY COLUMN? Warren Excel Worksheet Functions 2 June 30th 06 09:53 AM
Inputting data to one worksheet for it effect another daedalus1 Excel Discussion (Misc queries) 1 June 25th 06 04:39 PM
Is there an easy way to count when adjacent cells contain same data? mrarcade Excel Worksheet Functions 1 August 3rd 05 10:36 AM
Convert data type of cells to Text,Number,Date and Time Kevin Excel Worksheet Functions 1 December 31st 04 01:57 PM


All times are GMT +1. The time now is 12:09 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"