Solved: countif query & more...

[parttime_guy]

Hi All,

Plz review the attached file, I need to know if the countif query can give the attached results. If yes, plz guide me.

Thx-n-BR

[RichardSchollar]

Hi

Unfortunately you haven't shown the logic behind the results you need - how, for example, do you arrive at 3 Clients for Emp Name 111? Or 4 Clients for Emp Name 222?

Richard

[parttime_guy]

Yo Richard,

Thanks for showing interest!

If you filter Column H (Emp. Name) by ?111? you should get all clients in Column G (Clients). Now for the tricky part - even though Column G has a list of clients and there are 5 entries for Aaa it should be considered as 1 and so on?)

Eg:
Number of clients for 111 is 3
(Aaa = 5, Bbb = 3, Ccc = 1) the total count of unique clients for 111 are 3.

Number of clients for 222 is 4
(Aaa = 2, Bbb = 2, Ccc = 1, Ddd = 1) the total count of unique clients for 222 are 4.

Number of clients for 333 is 1
(Bbb = 2) the total count of unique clients for 333 is 1

Number of clients for 444 is 3
(Aaa = 2, Ccc = 1, Ddd = 3) the total count of unique clients for 444 are 3

Iam up against a :banghead: wall plz suggest something.

BR

[RichardSchollar]

Hi BR

One formula you can use is this (eg in K16 copied down):

=SUM(IF(FREQUENCY(IF($G$2:$G$25<>"",IF($H$2:$H$25=J16,MATCH($G$2:$G$25,$G$2:$G$25,0))),ROW($G$2:$G$25)-ROW($G$2)+1),1))

This is an array formula which must be confirmed with Ctrl+Shift+Enter (not just enter) - Excel will surround the formula with curly braces if entered correctly (do NOT manually enter these yourself).

Please also see attached.

Best regards

Richard

[Shazam]

Input formula in cell K2 and copy down.

=COUNT(1/(IF($G$2:$G$25<>"",MATCH($G$2:$G$25,IF($H$2:$H$25=J16,$G$2:$G$25),0)-1+ROW($G$2)=ROW($G$2:$G$25))))

The formula is an array need to hold down,


Ctrl,Shift,Enter


Hope it helps!

[parttime_guy]

Yo Richard,
Thanks this works!
Best regards
:thumb -n- :friends: