Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula help! counting one visit clients
Ok, i have this formula that works for me counting only the Clients in
column A$A, but i want do add another column to it. And for some reason it does not work, the "january" column. =SUMPRODUCT(--(COUNTIFA1:A65530,A1:A65530)=1)) I want the formula to look in "january" column and based on Column "client name" to tell me which client in Janary is typed in only once. in bottom example woul be Mike and Laura to be correct, ( and add them as 2 clients) id dont need their names. So it should be total of 2 clients have come in Janary based on all the clients in A$A column "CLIENT NAMES" "January" A1: A65530 B1:B200 John Doe Mike Mcarthur Chris Jones Laura Williams Joh Doe John Doe Chris Jones Dana Berindei |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula help! counting one visit clients
I clarified it a bit more..
Ok, i have this formula that works for me counting only the Clients in column A$A, but i want do add another column to it. And for some reason it does not work, the "january" column. =SUMPRODUCT(--(COUNTIFA1:A65530,A1:A65530)=1)) I want the formula to look in "january" column and based on Column "client name" to tell me which client in Janary is typed in only once. in bottom example woul be Mike and Laura to be correct, ( and add them as 2 clients) id dont need their names. So it should be total of 2 clients have come in Janary based on all the clients in A$A column "CLIENT NAMES" "January" A1: A65530........................................B1:B 200 John Doe ............................................Mike Mcarthur Chris Jones .........................................Laura Williams Joh Doe ..............................................John Doe Chris Jones Dana Berindei |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula help! counting one visit clients
Do you want to count the names in January which are also there in names column?
Can January (Col B) have one name more than once? Can Names (Col A) have one name more than once? One way... You can enter this next to January column (in C1) =(ISNA(VLOOKUP(B1,A:A,1,False),"",1)) and copy down Sum of column C will give you the count of names in January which are also there in Names... If names can be repeated in Col B then use =SUMIF(B1:B100,B1,C:C) and then sum only cells with 1 ... -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "Jman" wrote: I clarified it a bit more.. Ok, i have this formula that works for me counting only the Clients in column A$A, but i want do add another column to it. And for some reason it does not work, the "january" column. =SUMPRODUCT(--(COUNTIFA1:A65530,A1:A65530)=1)) I want the formula to look in "january" column and based on Column "client name" to tell me which client in Janary is typed in only once. in bottom example woul be Mike and Laura to be correct, ( and add them as 2 clients) id dont need their names. So it should be total of 2 clients have come in Janary based on all the clients in A$A column "CLIENT NAMES" "January" A1: A65530........................................B1:B 200 John Doe ............................................Mike Mcarthur Chris Jones .........................................Laura Williams Joh Doe ..............................................John Doe Chris Jones Dana Berindei |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula help! counting one visit clients
Can January (Col B) have one name more than once? yes
Can Names (Col A) have one name more than once? yes When i enter the =(ISNA(VLOOKUP(B1,A:A,1,False),"",1)) formula it says i've entered too many arguments. "Sheeloo" wrote: Do you want to count the names in January which are also there in names column? Can January (Col B) have one name more than once? Can Names (Col A) have one name more than once? One way... You can enter this next to January column (in C1) =(ISNA(VLOOKUP(B1,A:A,1,False),"",1)) and copy down Sum of column C will give you the count of names in January which are also there in Names... If names can be repeated in Col B then use =SUMIF(B1:B100,B1,C:C) and then sum only cells with 1 ... -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "Jman" wrote: I clarified it a bit more.. Ok, i have this formula that works for me counting only the Clients in column A$A, but i want do add another column to it. And for some reason it does not work, the "january" column. =SUMPRODUCT(--(COUNTIFA1:A65530,A1:A65530)=1)) I want the formula to look in "january" column and based on Column "client name" to tell me which client in Janary is typed in only once. in bottom example woul be Mike and Laura to be correct, ( and add them as 2 clients) id dont need their names. So it should be total of 2 clients have come in Janary based on all the clients in A$A column "CLIENT NAMES" "January" A1: A65530........................................B1:B 200 John Doe ............................................Mike Mcarthur Chris Jones .........................................Laura Williams Joh Doe ..............................................John Doe Chris Jones Dana Berindei |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula help! counting one visit clients
Ok .. this is how it finally worked.. Your formula did help though.
I used your formula up to this point. In C1 i copied it down. =(ISNA(VLOOKUP(B1,A:A,1,False))) In D1 copied down =IF(C1=TRUE,"1",0) Then i just sum the 1's as you suggested. Thanks for the info. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula help! counting one visit clients
There should have been an IF around the formula I had given. Sorry, I don't
know how that happened... Probably mistake in cut and paste... Glad you figured it out yourself... that is always better... Let me know if you need any further assistance... -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "Jman" wrote: Ok .. this is how it finally worked.. Your formula did help though. I used your formula up to this point. In C1 i copied it down. =(ISNA(VLOOKUP(B1,A:A,1,False))) In D1 copied down =IF(C1=TRUE,"1",0) Then i just sum the 1's as you suggested. Thanks for the info. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula help! counting one visit clients
Jman,
Just cruising by here .. Wonder whether my response in your other thread: Transfer Per catagory .... did get through to you? Here's a direct link to that thread: http://tinyurl.com/6n65nm -- Max Singapore http://savefile.com/projects/236895 Downloads:19,500 Files:362 Subscribers:62 xdemechanik --- |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula help! counting one visit clients
I wasn't getting much replys and gave up on checking it after a few day..
Thanks for working on it Max, i can see you put much effort in it.. I'll give it a try tomorrow and i'l llet you know how it turned out. "Max" wrote: Jman, Just cruising by here .. Wonder whether my response in your other thread: Transfer Per catagory ... did get through to you? Here's a direct link to that thread: http://tinyurl.com/6n65nm -- Max Singapore http://savefile.com/projects/236895 Downloads:19,500 Files:362 Subscribers:62 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Named Formula Weakly Interacts with a Variable Range on the Worksheet - Re-Visit | Excel Discussion (Misc queries) | |||
Antique 1912-1949 Porcelain teapot 528 chinaware 400, Website extreme security invitation visit | Excel Worksheet Functions | |||
Identifying the last visit | Excel Worksheet Functions | |||
14 Clients | Excel Discussion (Misc queries) | |||
Match a name to all clients? | Excel Worksheet Functions |