regexmatch doesn't seem to work with minifs in Google Sheets-CodePudding

I'm trying to find the min date for sub-tasks that are related to the same Task ID.

Because I want to leave the task min date cell blank if none of the sub-tasks have a date entered, I use the following formula: =if(SUMPRODUCT(regexmatch($A18:$A,"^Sub-Task "&$B16&".[0-9]"),$F18:$F=$F16,G18:G<>"")<>0,minifs(G18:G,$A18:$A,regexmatch($A18:$A,"^Sub-Task "&$B16&".[0-9]"),$F18:$F,$F16,G18:G,"<>"),"")

This breaks downs as follows:

In the SUMPRODUCT function

I use regexmatch($A18:$A,"^Sub-Task "&$B16&".[0-9]*") to check that I only look at sub-tasks that have the same ID as the specific task "B16"
I use $F18:$F=$F16 to check that I only look for "Planned" dates, which is in "F16" instead of "Actual" dates
I use G18:G<>"" to check that I only look for date cells that aren't empty

If the sumproduct results in something, I then use the minifs() function to find the min value of the result.

IF the sumproduct results in nothing, I enter blank "" in the cell

The sumproduct seems to work perfectly well and gives me the results that I expect when I change values around, but the minifs function doesn't seem to work with regexmatch()

Is there a different syntax that has to be used in minifs functions?

CodePudding user response：

try:

REGEXMATCH(A18:A&"", "^Sub-Task "&B16&".*")

inserting numeric value in regex will cause VALUE error

CodePudding user response：

Figured it out, minifs can take regex directly, but only simple ones; it doesn't like characters like "^", and I was able to work around it for my needs.