Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
ISNUMBER & SEARCH problem
Got a sheet with formula
=SUMPRODUCT(--(ISNUMBER(SEARCH("yes",raw_data!$D$2:$D$8002))*(--(ISNUMBER(SEARCH(D2,raw_data!$U$2:$U$8002)))))) - basically it is searching another sheet for number of 'yes' responses from group of people in D2 (i.e. sales). Works fine but is counting "sales & marketing", "web sales" etc - I only want it to count lines that have a "yes" and "sales" only - hope that makes sense. Any help gratefully rec'd. -- DJ |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
ISNUMBER & SEARCH problem
.. I only want it to count lines that have a "yes" and "sales" only
Perhaps this suffices: =SUMPRODUCT((raw_data!$D$2:$D$8002="yes")*(raw_dat a!$U$2:$U$8002=D2)) where D2 contains: sales aha? hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "DJ" wrote: Got a sheet with formula =SUMPRODUCT(--(ISNUMBER(SEARCH("yes",raw_data!$D$2:$D$8002))*(--(ISNUMBER(SEARCH(D2,raw_data!$U$2:$U$8002)))))) - basically it is searching another sheet for number of 'yes' responses from group of people in D2 (i.e. sales). Works fine but is counting "sales & marketing", "web sales" etc - I only want it to count lines that have a "yes" and "sales" only - hope that makes sense |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
ISNUMBER & SEARCH problem
tried and it didn't work but I think I know why; I made a slight error in my
initial Q. I need to be able to count "sales" and "sale" - basically anything need to add wildcard at end of "sale" - tried in your calc and it didn't work - any idea? -- DJ "Max" wrote: .. I only want it to count lines that have a "yes" and "sales" only Perhaps this suffices: =SUMPRODUCT((raw_data!$D$2:$D$8002="yes")*(raw_dat a!$U$2:$U$8002=D2)) where D2 contains: sales aha? hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "DJ" wrote: Got a sheet with formula =SUMPRODUCT(--(ISNUMBER(SEARCH("yes",raw_data!$D$2:$D$8002))*(--(ISNUMBER(SEARCH(D2,raw_data!$U$2:$U$8002)))))) - basically it is searching another sheet for number of 'yes' responses from group of people in D2 (i.e. sales). Works fine but is counting "sales & marketing", "web sales" etc - I only want it to count lines that have a "yes" and "sales" only - hope that makes sense |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
ISNUMBER & SEARCH problem
Try
=SUMPRODUCT((raw_data!$D$2:$D$8002="yes")*(ISNUMBE R(SEARCH("sale",raw_data!$U$2:$U$8002)))) search string *sale* If this post helps click Yes --------------- Jacob Skaria "DJ" wrote: tried and it didn't work but I think I know why; I made a slight error in my initial Q. I need to be able to count "sales" and "sale" - basically anything need to add wildcard at end of "sale" - tried in your calc and it didn't work - any idea? -- DJ "Max" wrote: .. I only want it to count lines that have a "yes" and "sales" only Perhaps this suffices: =SUMPRODUCT((raw_data!$D$2:$D$8002="yes")*(raw_dat a!$U$2:$U$8002=D2)) where D2 contains: sales aha? hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "DJ" wrote: Got a sheet with formula =SUMPRODUCT(--(ISNUMBER(SEARCH("yes",raw_data!$D$2:$D$8002))*(--(ISNUMBER(SEARCH(D2,raw_data!$U$2:$U$8002)))))) - basically it is searching another sheet for number of 'yes' responses from group of people in D2 (i.e. sales). Works fine but is counting "sales & marketing", "web sales" etc - I only want it to count lines that have a "yes" and "sales" only - hope that makes sense |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
ISNUMBER & SEARCH problem
If source cells could contain either of these 2 words:
.. need to be able to count "sales" and "sale" My ventu =SUMPRODUCT((raw_data!$D$2:$D$8="yes")*(ISNUMBER(M ATCH(TRIM(raw_data!$U$2:$U$8),{"sale","sales"},0)) )) Test it out .. -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "DJ" wrote: tried and it didn't work but I think I know why; I made a slight error in my initial Q. I need to be able to count "sales" and "sale" - basically anything need to add wildcard at end of "sale" - tried in your calc and it didn't work - any idea? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine: ISNUMBER(SEARCH( and EXACT( ? | Excel Discussion (Misc queries) | |||
SUMPRODUCT + ISNUMBER(SEARCH) + Empty Cells | Excel Discussion (Misc queries) | |||
Limit to nested IF(ISNUMBER(SEARCH)) functions? | Excel Worksheet Functions | |||
problem with =isnumber(match(right(... | Excel Worksheet Functions | |||
Problem with Array Formulas and ISNUMBER | Excel Worksheet Functions |