Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting multiple values (including blanks) in one column
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting multiple values (including blanks) in one column
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting multiple values (including blanks) in one column
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 --- |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting multiple values (including blanks) in one column
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting multiple values (including blanks) in one column
.. 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 --- |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting multiple values (including blanks) in one column
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting multiple values (including blanks) in one column
Glad you got it working fine, Paul. You're welcome.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- <paultedder wrote 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting multiple values (including blanks) in one column
On Mar 9, 3:40 pm, "Max" wrote:
Glad you got it working fine, Paul. You're welcome. -- Max Singaporehttp://savefile.com/projects/236895 xdemechanik --- <paultedder wrote 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 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'!D2:D4557))*((('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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting multiple values (including blanks) in one column
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. -- David Biddulph wrote in message ... On Mar 9, 3:40 pm, "Max" wrote: 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'!D2:D4557))*((('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 Glad you got it working fine, Paul. You're welcome. -- Max Singaporehttp://savefile.com/projects/236895 xdemechanik --- <paultedder wrote 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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting multiple values (including blanks) in one column
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 Singapore http://savefile.com/projects/236895 xdemechanik --- |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting multiple values (including blanks) in one column
On Mar 10, 9:58 am, "Max" wrote:
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting multiple values (including blanks) in one column
welcome, good to hear that.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- <paultedder wrote Thanks Guys, it works perfectly Paul |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting multiple values (including blanks) in one column
On Mar 10, 11:15*pm, "Max" wrote:
welcome, good to hear that. -- Max Singaporehttp://savefile.com/projects/236895 xdemechanik --- <paultedder wrote Thanks Guys, it works perfectly Paul- Hide quoted text - - 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'!D2:D4557)+(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 |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting multiple values (including blanks) in one column
Maybe try changing this part of it in your expression to:
... *(ISERROR(SEARCH("shine*",'grid data'!D2:D4557))*(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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- <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'!D2:D4557)+(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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average not including Zeros/Blanks | Excel Worksheet Functions | |||
Adding multiple values in one column based on multiple values of the same value (text) in another column | Excel Discussion (Misc queries) | |||
lookup on Table including blanks - | Excel Worksheet Functions | |||
If Then, not using values, or not counting blanks | Excel Worksheet Functions | |||
Counting numbers in a column without including others | Excel Worksheet Functions |