Hi guys, I hope you can help me with what I think is a simple solution
but dammed if I can find the solution.... I have the following
spreadsheet

In column A are a list of names, there are duplicate of the same name
but each one is for the same person. eg 10 x Fred, 10 x Bob, 10 x Dave

In Column B is a list of company names, again some are duplicates or
similar eg Bobs Firm or Bobs Company

In column C are a list of codes including blanks eg AA, BB , CC, DD

I am struggling to find a formula which will do the following

Count the number of times that Fred appears, but where the company
does not include the word "bob" and only where there is a value of AA
or blank in column C.

Any suggestions gratefully received

Paul

Count the number of times that Fred appears, but where the company
does not include the word "bob" and only where there is a value of AA
or blank in column C.

In say, D2:
=SUMPRODUCT((A2:A10="Fred")*(B2:B10<"bob")*((C2:C 10="AA")+(C2:C10="")))
Adapt the ranges to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
wrote in message
...
Hi guys, I hope you can help me with what I think is a simple solution
but dammed if I can find the solution.... I have the following
spreadsheet

In column A are a list of names, there are duplicate of the same name
but each one is for the same person. eg 10 x Fred, 10 x Bob, 10 x Dave

In Column B is a list of company names, again some are duplicates or
similar eg Bobs Firm or Bobs Company

In column C are a list of codes including blanks eg AA, BB , CC, DD

I am struggling to find a formula which will do the following

Count the number of times that Fred appears, but where the company
does not include the word "bob" and only where there is a value of AA
or blank in column C.

Any suggestions gratefully received

Paul

Refinement, closer interp on this line
does not include the word "bob" ..
implies that "bob" could be part of a text string in col B

Try in D2:
=SUMPRODUCT((A2:A10="Fred")*(ISERROR(SEARCH("bob", B2:B10))*((C2:C10="AA")+(C2:C10=""))))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

On Mar 7, 4:01 pm, "Max" wrote:
Refinement, closer interp on this line does not include the word "bob" ..

implies that "bob" could be part of a text string in col B

Try in D2:
=SUMPRODUCT((A2:A10="Fred")*(ISERROR(SEARCH("bob", B2:B10))*((C2:C10="AA")+(C2:C10=""))))
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik
---

Max
Thanks for you advice, and yes your interpretation was correct, it
works a treat and does not count any appearance of the word "bob" as
part of a text string. It still does not count blanks in column C. Any
further advice gratefully received

Paul

.. It still does not count blanks in column C

This part in the expression should have taken care of it:
..+(C2:C10="")..

Perhaps there are white space(s) in the cells,
these cells might appear "blank" but are not really so.

Try wrapping a TRIM around the range, viz try:
=SUMPRODUCT((A2:A10="Fred")*(B2:B10<"bob")*((C2:C 10="AA")+(TRIM(C2:C10)="")))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

On Mar 9, 2:34 am, "Max" wrote:
.. It still does not count blanks in column C

This part in the expression should have taken care of it:

..+(C2:C10="")..

Perhaps there are white space(s) in the cells,
these cells might appear "blank" but are not really so.

Try wrapping a TRIM around the range, viz try:
=SUMPRODUCT((A2:A10="Fred")*(B2:B10<"bob")*((C2:C 10="AA")+(TRIM(C2:C10)="")))
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik
---

Max
you are a champion. I used the following and it works a treat
=SUMPRODUCT(('Grid data'!B9:B4563=A9)*(ISERROR(SEARCH("bob*",'Grid
data'!D9:D4563))*(('Grid data'!F9:F4563="aa")+(TRIM('Grid data'!
F9:F4563)=""))))

Thanks very much Max
Paul