Tech Support banner

Status
Not open for further replies.
1 - 1 of 1 Posts

·
Registered
Joined
·
95 Posts
Discussion Starter #1
Excel 2010

In my workbook, the data on Sheet 2 is:
column A - customer #
column B - service tech name
column C - service performed
column D - service performed
column E - service performed
column F - service performed

I need to count how many times a service tech performed each service.

Since each customer may have more than 1 service performed, the 1st service performed is listed in column C. Then, the next service is in column D and the next in column E, then F.

For example:
Customer # 123456
Service tech: Jim
Service performed: tire rotation
Service performed: oil change

Customer # 456789
Service tech: Jim
Service performed: tire balance
Service performed: tire rotation

I need to know how many tire rotations Jim performed, no matter what column the service is listed in.

The data will go into Sheet1. Column A has all of the services listed. The service tech names are across line #1, starting at column B. Therefore, if Jim is listed first and tire rotation is listed first, I need the count to appear in B2.

Here are 2 different formulas I have tried and neither one works properly (should the first formula be "OR" instead of "+"?):

=COUNTIFS('SHEET2'!$B:$B,B$1)+COUNTIFS('SHEET2'!$C:$C,$A2)+COUNTIFS(SHEET2'!$D:$D,$A2)+COUNTIFS(SHEET2'!$E:$E,$A2)+COUNTIFS('SHEET2'!$F:$F,$A2)


=COUNTIFS('SHEET2'!$B:$B,B$1,'SHEET2'!$C:$C,$A2)+COUNTIFS('SHEET2'!$B:$B,B$1,'SHEET2'!$D:$D,$A2)+COUNTIFS('SHEET2'!$B:$B,B$1,'SHEET2'!$E:$E,$A2)+COUNTIFS('SHEET2'!$B:$B,B$1,'SHEET2'!$F:$F,$A2)


Can anyone, please, help me?

Thank you!!
 
1 - 1 of 1 Posts
Status
Not open for further replies.
Top