Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Count Number of Months String Contains Based on Criteria

I’m looking for a formula to provide me with a count of the number of months that meet certain criteria.

Here is an example of the cells that would need to be evaluated:

Months
2024 May, 2023 October, 2023 August, 2022 December, 2022 October
2024 February, 2023 August, 2023 May, 2022 December, 2022 October
2023 October, 2023 August, 2023 May, 2023 January, 2022 October
2024 May, 2024 March, 2024 February, 2023 January, 2022 October
2024 March, 2024 February, 2023 December, 2023 October, 2023 May
2023 October, 2023 August, 2023 June, 2023 May, 2022 October
2024 May, 2023 October, 2023 May, 2023 March, 2023 January
2023 August, 2023 May, 2023 January, 2022 October

The user will be selecting a month from a drop down. Let’s say they select 2023 October. I will need the formula to count any cell that contains that month or newer. Result should look like this:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

Months Count
2024 May, 2023 October, 2023 August, 2022 December, 2022 October 2
2024 February, 2023 August, 2023 May, 2022 December, 2022 October 1
2023 October, 2023 August, 2023 May, 2023 January, 2022 October 1
2024 May, 2024 March, 2024 February, 2023 January, 2022 October 3
2024 March, 2024 February, 2023 December, 2023 October, 2023 May 4
2023 October, 2023 August, 2023 June, 2023 May, 2022 October 1
2024 May, 2023 October, 2023 May, 2023 March, 2023 January 2
2023 August, 2023 May, 2023 January, 2022 October 0

Other Notes:

  1. The months will always been in order (Recent to Oldest)
  2. Format of months will always be 4-Digit Year, Full Month Name
  3. Location of user selected month: Let’s say Settings!A1
  4. Location of cells to be evaluated: Data!A2:A
  5. Location of cells with counts: Data!B2:B

>Solution :

Here’s one approach you may test out:

=map(A2:A,lambda(Σ,if(Σ="",,let(Λ,split(Σ,", ",),countif(Λ,">="&A1)))))

enter image description here

  • Adjust ranges in the formula as needed in your sheet
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading