Is the below possible in Excel for Mac?
I have a sheet containing 10s of 1000s of records and I need to repeatedly do analysis on it to count the total incidence of substrings within different samples of the data.
I need to know the:
total count of records:
- where Record.FreeTextNotes contains any of a list of substrings
AND - where the Record.Category = the selected category/s; AND the Record.SubCategory = the selected subcategory/s
(I need to avoid double counting records where FreeTextNotes contain more than one of the substrings)
The substrings are variable and depend on the category being analysed and there could be up to 20 of them.
| ID | Category | Subcategory | FreeTextNotes |
|---|---|---|---|
| 1 | Category 1 | Subcategory 1.1 | Lorum ipsum |
| 2 | Category 1 | Subcategory 1.2 | Lorum Y ipsum |
| 3 | Category 2 | Subcategory 2.1 | Lorum ipsum Z |
| 4 | Category 3 | Subcategory 3.1 | X Lorum ipsum Z |
| 5 | Category 3 | Subcategory 3.1 | XY Lorum ipsum Z |
| — | ——– | ——– | |
| 6 | Category 1 | Subcategory 1.1 | Null |
| 7 | Category 1 | Subcategory 1.2 | Lorum ipsum |
| 8 | Category 2 | Subcategory 2.1 | LXrum iYsum XYZ |
| 9 | Category 4 | Subcategory 4.1 | X |
| 10 | Category 3 | Subcategory 3.1 | XoYum Zpsum |
I’m a reasonably advanced Excel user, but this is beyond me.
This is as far as I’ve got which finds 2 substrings at a time, but doesn’t restrict the results by category and subcategory.
=SUMPRODUCT(–((ISNUMBER(FIND(SubstringCellRef1,_FreeTextNotes)) + ISNUMBER(FIND(SubstringCellRef2,_FreeTextNotes)))>0))
Rachel is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.