Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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." |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching alfanumaric cell contents and returning with a numaric v | Excel Discussion (Misc queries) | |||
Searching a cell for certain contents | Excel Programming | |||
Searching a cells contents? | Excel Worksheet Functions | |||
Searching in another workbook for a string and returning with the contents of the cell next to it. | Excel Worksheet Functions | |||
Data searching based on cell contents using VBa | Excel Programming |