Find a Substring in SUMPRODUCT Array

[MobiusTrip]

Hi all,

I'm working with this formula

=SUMPRODUCT(--('all data'!$D$2:$D$501="All"),--('all data'!$E$2:$E$501=$A7),--('all data'!$G$2:$G$501=D$5))

The problem is, sometimes D$5 is only a substring of a cell in 'all data'!$G$2:$G$501. How do I tell the formula to count all the times that D$5 appears in the specified range, either as the entirety of a cell's contents or as a substring within a cell?

Substrings in cells in G2:G501 are separated by commas, so there is also the option to expand that single column into several columns. But changing the range to, say, 'all data'!$G$2:$K$501=D$5 returns an error. Also, this would require manual input to convert text to columns, and I'd like to keep this spreadsheet as automatic as possible (after copying and pasting items from a database into the 'all data' sheet for portability).

Thanks!

[NBVC]

Try:

=SUMPRODUCT(--('all data'!$D$2:$D$501="All"),--('all data'!$E$2:$E$501=$A7),--(ISNUMBER(SEARCH(D$5,'all data'!$G$2:$G$501))))

[MobiusTrip]

That works, thanks!

As a side note, in case anyone runs into a similar problem in the future, I thought that I had tried that earlier (I searched around a bit before posting). It turns out that I had "...--ISNUMBER(..." instead of "...--(ISNUMBER(...". Big difference in results.

Thanks again!

[DonkeyOte]

As a side note, in case anyone runs into a similar problem in the future, I thought that I had tried that earlier (I searched around a bit before posting). It turns out that I had "...--ISNUMBER(..." instead of "...--(ISNUMBER(...". Big difference in results.

In reality I suspect there were other issues... the parentheses encasing the ISNUMBER are not technically required:

=SUMPRODUCT(--('all data'!$D$2:$D$501="All"),--('all data'!$E$2:$E$501=$A7),--ISNUMBER(SEARCH(D$5,'all data'!$G$2:$G$501)))

will work just the same.