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 |
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 |
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 |
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 |
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. |
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. |
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 --- |
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 --- |
All times are GMT +1. The time now is 05:09 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com