Count if - text could be in one of 6 columns per row

[confused in Frankfurt]

Hi there,

I have a set of data where the sales rep. creates a visit report. His name can appear in one of 6 fields.

I want to count the number of visits and only once per sales rep. per visit. The name can appear on one of 6 columns and not always in the first.

How can I count the first and move on to the next until reaching the last column without counting more than once per line? I also have a date span to query as well in order to count per quarter. Additionally in separate columns I have information as to what type of visit took place which I also need to count per rep.

I am not using <acronym title="visual basic for applications">VBA</acronym> and need a formula for Excel in the 2010 version.

Regards
and thanks in advance.

Report Creator	Attendee 1	Attendee 2	attendee3	attendee 4	attendee 5	Visit Date
John Black	Mary Jones	John Black				21.01.2014
John Black						15.01.2014
Mary Jones		Mary Jones	John Black			11.01.2014
James Green	James Green					11.01.2014
James Green	James Green	Mary Jones				01.04.2014

<tbody>
</tbody>

Results: Q1 Q2
John Black 3 0
Mary Jones 2 1
James Green 1 1

 

[VBA Geek]

your above table is in A1:G6

if H1:H4 is

Results:
John Black
Mary Jones
James Green

i1 = Q1
j1 = Q2

enter in i2

=SUM(($B$2:$F$6=$H2)*(CHOOSE(MONTH($G$2:$G$6),"Q1","Q1","Q1","Q2","Q2","Q2","Q3","Q3","Q3","Q4","Q4","Q4")=I$1))

and confirm with control shift enter