Excel lookup and count

I'm trying to do a lookup for customer ID# from column A in column D and then count column E to see how many dates that one customer incurred the service. Notice Column D & E listed the id and date multiple times.

For example, customer id# 999999 from column A I want to lookup in column D and if there is a match I want to count the incurred dates; in this case there are two dates 2/13/13 & 2/19/13. See my attached worksheet, I tried this formula =LOOKUP(A2,$D$2:$D$186,COUNT(IF(FREQUENCY(E2:$E$2:$E$186,$E$2:$E$186)>0,1))) and many other ways and got nothing. I'm hoping to the count from column E as 2 for customer id#999999 in column A.

Can someone please take a look at the worksheet (attached) and provide a formula that gives the result I'm looking for?

Thank you,
Who is Participating?
barry houdiniConnect With a Mentor Commented:
You can use this "array formula" in B2


confirmed with CTRL+SHIFT+ENTER and copied down

See attached

regards, barry
chekn3Author Commented:
Lookup and count in excel 2007.
chekn3Author Commented:
This is excatly what I was looking for. Thank you very much.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.