Count between Dates

Heather Szukis

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.

David Tutwiler

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.

David Tutwiler

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.

Heather Szukis

Thank you it worked!