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