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.

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Report Creator[/TD]
[TD]Attendee 1[/TD]
[TD]Attendee 2[/TD]
[TD]attendee3[/TD]
[TD]attendee 4[/TD]
[TD]attendee 5[/TD]
[TD]Visit Date
[/TD]
[/TR]
[TR]
[TD]John Black[/TD]
[TD]Mary Jones[/TD]
[TD]John Black[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]21.01.2014[/TD]
[/TR]
[TR]
[TD]John Black[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]15.01.2014[/TD]
[/TR]
[TR]
[TD]Mary Jones[/TD]
[TD][/TD]
[TD]Mary Jones[/TD]
[TD]John Black[/TD]
[TD][/TD]
[TD][/TD]
[TD]11.01.2014[/TD]
[/TR]
[TR]
[TD]James Green[/TD]
[TD]James Green[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11.01.2014[/TD]
[/TR]
[TR]
[TD]James Green [/TD]
[TD]James Green[/TD]
[TD]Mary Jones[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]01.04.2014[/TD]
[/TR]
</tbody>[/TABLE]


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