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

confused in Frankfurt

Board Regular
Joined
Oct 11, 2010
Messages
53
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 CreatorAttendee 1Attendee 2attendee3attendee 4attendee 5Visit Date
John BlackMary JonesJohn Black21.01.2014
John Black15.01.2014
Mary JonesMary JonesJohn Black11.01.2014
James GreenJames Green11.01.2014
James Green James GreenMary Jones01.04.2014

<tbody>
</tbody>


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

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
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
 
Last edited:

Forum statistics

Threads
1,136,318
Messages
5,675,057
Members
419,548
Latest member
wfarzand

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top