Help with Sumproduct searching cell contents
 

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

Help with Sumproduct searching cell contents
 

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:B F29783=Formula!B5)*(Extract!BK4:BK29783=Dashboard! C3))

Are you looking for a substring then try:
=SUM(IF((Extract!F4:F29783="Y")*(Extract!BF4:BF297 83=Formula!B5)*(ISNUMBER(SEARCH(Dashboard!C3,Extra ct!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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Help with Sumproduct searching cell contents
 

"Jay07" wrote:
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$29 783,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."