Count Data in two Columns

[briandhowells]

I have a spreadsheet of jobs worked by two different employees
structured like this...

Date Job Emp 1 Emp 2
1/1 Installation John Smith Rick Johnson
1/1 Installation Rick Johnson John Smith
1/2 Construction John Smith Eric Richards
1/3 Installation John Smith Rick Johnson

....The two employee fields represent unique jobs - so I need to keep
that data the way that it is for tracking purposes - but I'd also like
to get a count of how many times a pair of employees worked together
regardless of the job. In other words, the above would yield the
results

John Smith and Rick Johnson 3
John Smith and Eric Richards 1

Any thoughts? I'd also be open to something in Access if it isn't too
complicated.

Thanks!

 

[PCLIVE]

One way:

=SUMPRODUCT(--(B1:B4=J1),--(C1:C4=K1))

Column B has Employee 1 and Column C has Employee 2. J1 and K1 are the two
employees of which you want a count of when they worked together.

HTH,
Paul

 

[merjet]

Try this in E2:
=SUMPRODUCT(--(C2:C5=C2),--(D25=D2))+SUMPRODUCT(--(C2:C5=D2),--
(D25=C2))

Hth,
Merjet