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

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

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

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

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?