I've got the following formula that looks for certain keywords in cells within a column.
=SUMPRODUCT(COUNTIFS('Data'!$G:$G,{"*" & Settings!$E$5 & "*","*" & Settings!$E$6 & "*","*" & Settings!$E$7 & "*"}))
Here is an example of the data on the Data sheet.
Column G |
---|
2024 September, 2024 August |
2024 August |
2024 May |
2023 September |
2024 September, 2024 August, 2023 May |
2024 September, 2024 August, 2024 July |
And the settings sheet.
3rd Quarter Months |
---|
2024 July |
2024 August |
2024 September |
Using the above formula I get a count of occurrences for each of the months in the quarter. So I would get a count of 8. Instead, I need the count of cells that contains at least one of these months in the quarter. So the answer should be 4.
How can I achieve the count of cells that contain at least one of these months?
Edit: for the sake of simplicity I have trimmed this formula down to the problem area. I am using countifs
as opposed to countif
because I have other conditions that need to be met.
You may try (going with an assumption those'll are plain text formatted):
=countif(index(regexmatch(Data!G:G,join("|",Settings!E5:E7))),true)