Formula help! counting one visit clients

  • Thread starter Thread starter Jman
  • Start date Start date
J

Jman

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
 
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 ...
 
Can January (Col B) have one name more than once? yes
Can Names (Col A) have one name more than once? yes
Click to expand...


When i enter the =(ISNA(VLOOKUP(B1,A:A,1,False),"",1))

formula it says i've entered too many arguments.
 
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.
 
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...
 
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.
 
Back
Top