google-sheetsgoogle-sheets-formula

Formula is currently double-counting a cell


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.


Solution

  • You may try (going with an assumption those'll are plain text formatted):

    =countif(index(regexmatch(Data!G:G,join("|",Settings!E5:E7))),true)