Counting multiple values (including blanks) in one column

[paultedder]

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

 

[Max]

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:C10="AA")+(C2:C10="")))
Adapt the ranges to suit

---

 

[Max]

Refinement, closer interp on this lineimplies 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=""))))

---

 

[paultedder]

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

 

[Max]

.. 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:C10="AA")+(TRIM(C2:C10)="")))

---

 

[paultedder]

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


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:C10="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'!D94563))*(('Grid data'!F9:F4563="aa")+(TRIM('Grid data'!
F9:F4563)=""))))

Thanks very much Max
Paul

 

[Max]

Glad you got it working fine, Paul. You're welcome.

 

[paultedder]

Glad you got it working fine, Paul. You're welcome.

Max
Firstly sorry to keep bother you but you've started something in my
brain. After getting those results I now want to also look up a 3rd
value in the same column, the following is my formula but it is not
giving me the correct results, eg I am getting zero and I can
calculate that the answer is 7

SUMPRODUCT(('Grid data'!B2:B4557=A2)*(ISERROR(SEARCH("shine*",'Grid
data'!D24557))*((('Grid data'!F2:F4557="NS")*('Grid data'!
F2:F4557="C")*(TRIM('Grid data'!F2:F4557)="")))))

I am not getting an error so assume my parenthesis etc is ok ?

Paul

 

[David Biddulph]

Your term ...*((('Grid data'!F2:F4557="NS")*('Grid
data'!F2:F4557="C")*(TRIM('Grid data'!F2:F4557)="")))
is multiplying 1 or 0 for true or false for column F being "NS" by another
1 or 0 for true or false for column F being "C", and then by another 1 or 0
for true or false for column F being "".
The mutliplication gives a boolean AND function, and it returns a 1 only if
all 3 terms are 1. If any of the 3 terms are zero the result of the
multiplication is zero (or FALSE). If column F is "NS" it can't also be "C"
or "", so you answer will always be zero, as you've found. If you want OR,
rather than AND, then you need *addition*, rather than *multiplication*, as
in your earlier formula.

 

[Max]

Paul,

As David explained, an OR is required, expressed indicatively as:
((Cond1)+(Cond2)+(Cond3)+...)

Hence the part in your expression should look like this:
.... *(('Grid data'!F2:F4557="NS")+('Grid data'!F2:F4557="C")+
(TRIM('Grid data'!F2:F4557)="")))

---

 

[paultedder]

Paul,

As David explained, an OR is required, expressed indicatively as:
((Cond1)+(Cond2)+(Cond3)+...)

Hence the part in your expression should look like this:
... *(('Grid data'!F2:F4557="NS")+('Grid data'!F2:F4557="C")+
(TRIM('Grid data'!F2:F4557)="")))
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik
---

Thanks Guys, it works perfectly
Paul

 

[Max]

welcome, good to hear that.

 

[paultedder]

welcome, good to hear that.
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik
---
<paultedder wrote>




- Show quoted text -

Promise this will be the last question. You've given me some ideas and
I'm going crazy with alternate options.

On 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'!D24557)+(SEARCH( "non*",'Grid data'!E2:E4557))*((('Grid data'!
F2:F4557="NS")+('Grid data'!F2:F4557="C")+(TRIM('Grid data'!
F2:F4557)="")))))))

With your expert eye, can you see the issue as I'm wearing my nails
down trying to see it

Paul

 

[Max]

Maybe try changing this part of it in your expression to:

... *(ISERROR(SEARCH("shine*",'grid
data'!D24557))*(ISERROR(SEARCH("non*",'grid data'!E2:E4557))* ...

.. Promise this will be the last question.

No need for such promises. You could always put in any new queries as fresh
new postings, which is the usual route to take once the original query is
answered, and the thread closed. I may not always be around or be able to
help, but there are **many** other responders out there who would be in a
position to do so and who enjoy doing so. Putting in as new postings would
immediately surface your new query to their ever-attentive radar.

---
<paultedder wrote
Promise this will be the last question. You've given me some ideas and
I'm going crazy with alternate options.

On 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'!D24557)+(SEARCH( "non*",'Grid data'!E2:E4557))*((('Grid data'!
F2:F4557="NS")+('Grid data'!F2:F4557="C")+(TRIM('Grid data'!
F2:F4557)="")))))))

With your expert eye, can you see the issue as I'm wearing my nails
down trying to see it

Paul