# 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,
Book1.xlsx
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Author Commented:
Lookup and count in excel 2007.
0
Commented:
You can use this "array formula" in B2

=SUM(IF(FREQUENCY(IF(D\$2:D\$186=A2,E\$2:E\$186),E\$2:E\$186),1))

confirmed with CTRL+SHIFT+ENTER and copied down

See attached

regards, barry
different-dates.xlsx
0

Experts Exchange Solution brought to you by