Help with Sumproduct searching cell contents

[Jay07]

Using a sumproduct, across 29783 rows I'm trying to count how many row
have a "Y" in one column, a specific word in another column and if
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 Shee
(Dashboard) and is in cell C3. The column containing strings of text i
in the Extract worksheet - column BK.

Examples of data in column BK are:

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 roo
names.


Any help greatly appreciated.

Jaso

 

[Claus Busch]

Hi Jay,

Am Mon, 11 Nov 2013 13:19:42 +0000 schrieb Jay07:

=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.

are you searching for the exact word in BK? Then try:
=SUMPRODUCT((Extract!F4:F29783="Y")*(Extract!BF4:BF29783=Formula!B5)*(Extract!BK4:BK29783=Dashboard!C3))

Are you looking for a substring then try:
=SUM(IF((Extract!F4:F29783="Y")*(Extract!BF4:BF29783=Formula!B5)*(ISNUMBER(SEARCH(Dashboard!C3,Extract!BK4:BK29783))),1))
or
=SUM((Extract!F4:F29783="Y")*(Extract!BF4:BF29783=Formula!B5)*(ISNUMBER(SEARCH(Dashboard!C3,Extract!BK4:BK29783))))
The last two formulas have to be array-entered with CTRL+Shift+Enter


Regards
Claus B.

 

[joeu2004]

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. [....] 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))))

Try:

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

Note that I dropped the ",0" parameter that you included for SEARCH. Note
the following statements from the SEARCH help page:

"If start_num is not greater than 0 (zero) or is greater than the length of
the within_text argument, the #VALUE! error value is returned."

and

"If the start_num argument is omitted, it is assumed to be 1."