Help with Sumproduct searching cell contents

[jwillis07]

Using a sumproduct, across 29783 rows I'm trying to count how many rows have a "Y" in one column, a specific word in another column and if a cell contains a certain word.

I've managed the first two and am confident it works.

=SUMPRODUCT((Extract!F$4:F$29783="Y")*(Extract!$BF $4:$BF$29783=Formula!$B5)

When it comes to the final condition I'm lost. My best guess is:

=SUMPRODUCT((Extract!F$4:F$29783="Y")*(Extract!$BF $4:$BF$29783=Formula!$B5)*(COUNT(SEARCH(Dashboard! $C$3,Extract!$BK$4:$BK$29783,0))))



For the third condition the text to search for is held in another Sheet (Dashboard) and is in cell C3. The column containing strings of text is in the Extract worksheet - column BK.

Examples of data in column BK a

P337 FT3DD Studio; P338 FT3DD Studio
P339 D FT3DD Studio; P339A FT3DD Studio; P339B FT3DD Studio


Text in Cell C3 of the Dashboard sheet could be any one of these room names.


Any help greatly appreciated.

Jason

[ragulduy]

=SUMPRODUCT((Extract!F$4:F$29783="Y")*(Extract!$BF $4:$BF$29783=Formula!$B5)*(NOT(ISERROR(FIND($C$3,$BK$4:$BK$29783,1)))))

[canapone]

Hi,

maybe

=SUMPRODUCT(--(Extract!F$4:F$29783="Y"),--(Extract!$BF $4:$BF$29783=Formula!$B5),--ISNUMBER(SEARCH(Dashboard! $C$3,Extract!$BK$4:$BK$29783,0)))

regards

[jwillis07]

Thanks guys for your speedy responses but unfortunately I've tried them both and they're both resulting in zeros which I know to be incorrect.

[canapone]

Hi All,


a small typo (in red) in the formula I shared

=SUMPRODUCT(--(Extract!F$4:F$29783="Y"),--(Extract!$BF$4:$BF$29783=Formula!$B5),--ISNUMBER(SEARCH(Dashboard!$C$3,Extract!$BK$4:$BK$29783,1)))


Hope it helps