Count between Dates

Hello I am trying to pull a few figures and am coming up empty on two situations.1.

  1. I would like to know the count of entries that have a date between 9-2-2021 & 9-30-2021. The range is correct where the figure would pull from. Below is the formula.
  2. The second question I don't' know where to start. I want to know how many figures are in CIP&KYC 2021 range that do NOT equal #N/A. For instance is there are 200 entries, where 20 are #N/A and the other 180 have a date, the answer should be 180.



Best Answer

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Answer ✓

    To test the solution I made a column with the same title as your cross sheet reference, but it should work the same. Here are the two formulas that will solve your questions.

    1) =COUNTIFS([CIP &KYC 2021]:[CIP &KYC 2021], <=DATE(2021, 9, 30), [CIP &KYC 2021]:[CIP &KYC 2021], >=DATE(2021, 9, 2))

    2) =COUNTIF([CIP &KYC 2021]:[CIP &KYC 2021], ISDATE(@cell))

    I simplified the second solution to just look for the dates. Should be the same as searching for NAs if all you have are dates and NAs in the column.

Answers

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Answer ✓

    To test the solution I made a column with the same title as your cross sheet reference, but it should work the same. Here are the two formulas that will solve your questions.

    1) =COUNTIFS([CIP &KYC 2021]:[CIP &KYC 2021], <=DATE(2021, 9, 30), [CIP &KYC 2021]:[CIP &KYC 2021], >=DATE(2021, 9, 2))

    2) =COUNTIF([CIP &KYC 2021]:[CIP &KYC 2021], ISDATE(@cell))

    I simplified the second solution to just look for the dates. Should be the same as searching for NAs if all you have are dates and NAs in the column.

  • Thank you it worked!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!