Count MAX Date Meeting Criteria in Cells Containing Multiple Dates
In Google Sheets, I am using the formula shown below to count dates meeting this criterion: >=7/1/2023
=SUM(ARRAYFORMULA(COUNTIF(IFERROR(SPLIT(REGEXREPLACE(IMPORTRANGE("[sheet id]","'Oct 2022'!$O$5:$O$200"),"[\n, ]",CHAR(10)),CHAR(10)),IMPORTRANGE("[sheet id]","'Oct 2022'!$O$5:$O$200")),">="&DATE(2023,7,1))))
The formula works fine, whether the cell contains a single date (e.g. 7/1/2023) or multiple dates (e.g. 6/30/2022, 7/1/2023, 8/22/2023, etc.).
How would I rewrite the formula so that it counts only the MAX date in a multi-date cell, so long as that MAX date meets the specified criteria?
UPDATE
For anyone who might benefit from a spreadsheet formula that counts dates — and/or parts of strings within a particular range that appear like dates — the formula shown in the original post above works as described. I have already used this formula productively on multiple projects.
What I am trying to do is adjust this formula so the parts of a string that look like dates within any particular cell in a given range are counted only if those "dates" are the most recent "date" within a cell containing more than one "date" within the string. In order to be counted, the "date" would also need to meet the criteria specified in the formula (e.g. >=7/1/2023).
In the image included here, all the dates shown in bold color are currently counted. My goal is for the formula to count only the dates shown in bold green.
This formula successfully accomplishes the result I sought in the question I posted above:
=ARRAYFORMULA(SUM(IF(LEN(O5:O200),--(REGEXMATCH(TO_TEXT(O5:O200),"(^|,|\s)\s*(" & TEXTJOIN("|",TRUE,ARRAYFORMULA(TEXT(ROW(INDIRECT("A"&DATE(2023,7,1)&":A"&TODAY())),"m/d/yyyy")))&")\s*($|,|\s)")),0)))
- How do I send email to user by appending my domain name to the user name in Google Apps Script?
- How to split comma-separated values across multiple rows dynamically?
- Replace multiple strings in Google Spreadsheet with script
- How to count the amount of times a substring appears in a range of cells?
- Query with multiple data types
- Count If Range Matches Lookup in another tab
- Google Sheets Query where data in either of two columns matches the value in a cell populated by a multi-select dropdown
- Trying to combine words and math in a single cell in google sheets
- Counting the previous sample over multiple conditions throughout the range
- How to randomly select by type probability all items from a google sheet column range
- Sort result of function by RHS column
- Google Sheets Query - using another query to filter dates
- Skip formula if sheet does not exist in Google Sheets
- How to get data from public google sheet and more than 100 rows
- In Google sheets how can I Script the manipulation of the default Zoom?
- Google Sheets IMPORTDATA for xlsx files
- conditional formatting to highlight the highest value in a row compared to all the columns before it
- Google sheets summation formula, iterating until a new header on column A
- Using ARRAYFORMULA and INDIRECT to iterate rows and return as a block
- How to scroll to bottom of a google spreadsheet using selenium
- Calculate average days between dates continuously updating
- Conditional formatting in Google Sheets is recognizing some cell values that equal 0, not others
- Model / Year formula fails when a sequence starts stops then starts again
- Google Sheets: Sumif formula
- Randomize cells in Google Sheets
- Google script for move rows depending on checkbox - Google sheet
- How do I splitTextToColumns only for cells containing commas in Google Apps Script?
- Getting a “Google hasn’t verified this app” warning when creating a trigger - ok to ignore it?
- Count in specific ways in Google sheets
- Fixing 1 row, and leaves each following row movable