Thread: Formula for Lookup and Count function in new Excel update

Having trouble getting a formula to work that will lookup a site number and count the number of times the site has a particular type of visit. For instance:

Site # Visit
1001 Active
1001 Active
1001 Discont.
1001 Screen Fail
1001 Active
1002 Discont.
1002 Active
1002 Screen Fail
1002 Active

I want a formula that will return the number of active instances for each site, like:

Site # # of Active Visits
1001 3
1002 2

Try Sumproduct()

e.g. =Sumproduct(($A$1:$A$10="1001")*($B$1:$B$100="Active"))

adjust ranges to suit.

You can replace the "1001" and/or "Active" with cell references containing those strings.