| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fresh pair of Eyes required
Hi,
with the help of some excellent feedback from the User Group I have created the following formula but I am not getting the expected results The formula works finre until I put the 2nd search for a particular word.. eg This works fine =SUMPRODUCT((('Grid data'!B2:B4557=A2)*(ISERROR(SEARCH("shine*",'Grid data'!D2:D4557)*((('Grid data'!F2:F4557="NS")+('Grid data'! F2:F4557="C")+(TRIM('Grid data'! F2:F4557)=""))))))) And counts correctly, ignoring anything with SHINE in column D and only including, blanks, C and NS values. The following formula I am not getting the right results. It appears to be ignoring the search for "NON" in column E. =SUMPRODUCT((('Grid data'!B2:B4557=A2)*(ISERROR(SEARCH("shine*",'Grid data'!D2:D4557)+(SEARCH( "non*",'Grid data'!E2:E4557))*((('Grid data'! F2:F4557="NS")+('Grid data'!F2:F4557="C")+(TRIM('Grid data'! F2:F4557)=""))))))) Paul |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| My eyes Are Tiered, Forehead Bruised, My Desk Dented.!! Help??? | Excel Discussion (Misc queries) | |||
| count and return pair numbers | Excel Worksheet Functions | |||
| Fonts Too Small or Eyes Too Big? | New Users to Excel | |||
| How do pair different data series. | Charts and Charting in Excel | |||
| in excel, how do I find which value doesn't have a pair? | Excel Discussion (Misc queries) | |||
Threaded Mode
