1

I am trying to parse a data set to determine the source of customer acquisition and also to determine the impact of marketing efforts. I am currently using an array of keywords to help identify and group sources. I would like to use these keywords used in multiple equations, but would like for the keywords to be defined in a single location.

With the keywords explicitly defined within the equation as shown below, I am seeing the appropriate number of matches found.

ArrayFormula(sum(countifs('Estimates Booked'!C:C, {"*angi*", "*homeadvisor*"})))

However, when I store the array of keywords in a cell and reference the cell, no matches are being found.

ArrayFormula(sum(countifs('Estimates Booked'!C:C, 'Lead Source Keywords'!A1)))

Is there a way to store this array of keyworks on a cell so it can be easily updated across multiple equations?

player0
  • 124,011
  • 12
  • 67
  • 124
Josiah Lund
  • 55
  • 7
  • 1
    Please use the appropriate tags, Google sheets in this case. – JvdV Apr 23 '22 at 05:58
  • Does this answer your question? [Excel: Searching for multiple terms in a cell](https://stackoverflow.com/questions/14803944/excel-searching-for-multiple-terms-in-a-cell). From [your answer](https://stackoverflow.com/a/71982815/3025856), it sounds like it does. If so, this question should be closed as a duplicate so the original question takes priority. – Jeremy Caney Apr 23 '22 at 19:07
  • 1
    @JeremyCaney Yes, this does answer the question as I originally asked it. I figured Google sheets would work the same as Excel, but that is not the case. I still have an issue since Google sheets does not have the `evaluate` function that Excel does. I agree this issue should be closed as duplicate/poorly focused. – Josiah Lund Apr 23 '22 at 19:26
  • 1
    With updated tags and title, this issue is related to the linked excel solution, but is now a standalone question specific to Google sheets. – Josiah Lund Apr 24 '22 at 18:45

2 Answers2

1

if it should be in one cell try:

=COUNTA(IFNA(FILTER('Estimates Booked'!C:C, 
 REGEXMATCH('Estimates Booked'!C:C, REGEXREPLACE('Lead Source Keywords'!A1, ",", "|")))))

where 'Lead Source Keywords'!A1 =

angi,homeadvisor

update:

=COUNTA(IFNA(FILTER(E:E, F:F>=TODAY(), 
 REGEXMATCH(E:E, REGEXREPLACE(REGEXREPLACE(B2, "[\{\} ""]", ), ",", "|")))))

enter image description here

or just use:

=SUMPRODUCT(F:F>=TODAY(), REGEXMATCH(E:E, "(?i)"&B3))
player0
  • 124,011
  • 12
  • 67
  • 124
  • Thanks for the answer. I've spent some time trying to get this implemented but have so far been unsuccessful. I believe this is closer to correct, but it's still being evaluated as #ERROR. `=COUNTA(IFNA(FILTER('Estimates Booked'!C:C, Estimates Booked'!C:C=REGEXREPLACE('Lead Source Keywords'!A1, ",", "|"))))` I got here by hard coding the criteria as: `=COUNTA(IFNA(FILTER('Estimates Booked'!C:C, Estimates Booked'!C:C="angi")))` Unfortunately, even if this worked, it would not solve my issue as I am searching for keywords within text using wildcards which filter doesn't support. – Josiah Lund Apr 23 '22 at 14:31
  • With a bit more time, I now have something that uses filters and wildcard searches. Only bit I'm still missing is how to use `REGEXREPLACE` to populate the criteria in a more dynamic way. I've also added a date filter that I originally left off the question for simplicity. `=COUNTA(FILTER('Estimates Booked'!C:C, isnumber(search("angi", 'Estimates Booked'!C:C))*('Estimates Booked'!D:D>TODAY())))` – Josiah Lund Apr 23 '22 at 14:52
  • @JosiahLund can you share a copy / sample of your sheet with an example of the desired output? – player0 Apr 23 '22 at 19:22
  • sure. Here is a sample that shows the equation I have and what I'd like to do with it. https://docs.google.com/spreadsheets/d/1DCxRuNPk8biiyxj6E5W26xeALH_PUFshVlxRpvAvXUU/edit#gid=0 – Josiah Lund Apr 23 '22 at 19:33
  • @JosiahLund sheet is private – player0 Apr 23 '22 at 19:41
  • permissions have been updated on the sheet. – Josiah Lund Apr 23 '22 at 19:49
  • @JosiahLund https://docs.google.com/spreadsheets/d/17SpeyY4ytH0TOnccSzQTuadgmhHCXfQRQUtybxlHjqo/edit#gid=0 – player0 Apr 23 '22 at 20:01
0

After additional searching, this question has already been answered in at https://stackoverflow.com/a/59421784/12926358 by using the EVALUATE function. This function does not exist in google sheets.

Josiah Lund
  • 55
  • 7