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

#### LG2

·
##### Registered
Joined
·
95 Posts
Discussion Starter
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)

Thank you!!

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