Share via

count excel date range: i.e. "-" =not started, "2 date range" =Complete, 1 date = in progress

Anonymous
2021-12-13T19:42:38+00:00

Hi Folks, I'm looking for some help trying to count my cells. I need simple counts. Each cell is autogenerated with start and finish dates combined into 1 cell.

I have over 2000 Rows, and over40 Columns that will autogenerate each day, so this is not possible to count by hand?

My Data is QTY of ::::

Complete: (12/05/2021-12/07/2021)

In progress: (12/05/2021 - )

Not Started: (-)

Skipped (skipped)

Can you pelase help provide suggestions?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. OssieMac 47,981 Reputation points Volunteer Moderator
    2021-12-13T21:59:20+00:00

    Don't know which columns you want to count but the following should point you in the right direction using COUNTIF and wild cards.

    I have not used your actual dates but inserted data that will return a different value for each condition just for the purpose of ensuring the formulas are correct.

    Following Formulas that you can copy rather than in the picture.

    Cell B20: =COUNTIF(B2:B17,"*/*/*-*/*/*")

    Cell B21: =COUNTIF(B2:B17,"*/*/*-")

    Cell B22: =COUNTIF(B2:B17,"-")

    Celll B23: =COUNTIF(B2:B17,"Skipped")

    Feel free to get back to me if still having problems with this.

    0 comments
  2. Anonymous
    2021-12-13T23:31:54+00:00

    Thank you so much, Can you please help me figure out how to highlight the cells that are IN progress?

    =COUNTIF(B2:B17,"*/*/*-")

    Also, How Can i count All Items for a grand total?

    Cell B20: =COUNTIF(B2:B17,"*/*/*-*/*/*")

    Cell B21: =COUNTIF(B2:B17,"*/*/*-")

    Cell B22: =COUNTIF(B2:B17,"-")

    Celll B23: =COUNTIF(B2:B17,"Skipped")

    0 comments
  3. OssieMac 47,981 Reputation points Volunteer Moderator
    2021-12-14T01:12:02+00:00

    I assume you are referring to Conditional Formattting. Therefore, you can use the COUNTIF function just for a single cell in the range and if the result is greater than zero (it will be 1 (one) for a single cell range then the cell matches.

    To apply the Conditional Formatting.

    1. Select the range required for the Conditional Formatting
    2. Select Conditional formatting (On the Home ribbon)
    3. Select New rule
    4. Select "Use a formula to determine which cells to format"
    5. In the field under the heading "Format values where this formula is true:", Insert the following formula where B2 is the first cell of the selected range.

    =COUNTIF(B2,"*/*/*-")>0

    To "count All Items for a grand total" use the COUNTA function which will count all non blank cells in the range like the following.

    =COUNTA(B2:B17)

    Feel free to get back to me if further assistance is still required.

    0 comments
  4. OssieMac 47,981 Reputation points Volunteer Moderator
    2021-12-14T01:23:39+00:00

    @Christine_Ronayne

    I have applied some editing to my last post so if you have already looked at it then I suggest you refresh the page. In one part I left out a step in the Conditional formatting and another part I thought was possibly confusing so I deleted the confusing part.

    This post is because you do not get an email notification of edits.

    0 comments