Count Specific values in a Pivot Table

Hi all!!

I have a table that includes 4 different types of data, Work Order #, Area, Date and Service Code. The problem is for each Work Order #, there is only 1 entry for Area, 1 entry for Date, but MANY entries for Service Codes for each work order.

I am trying to count the number of times a specific Service Code appears throughout the table of data, and then be able to break it down by Area.

I have the columns headers listed as the following: Work Order #, Area, Date, Service Code 1, Service Code 2, etc all the way to Service Code 12.

Example: the Service Code "AA” is listed for most Work Orders, but in all different columns. I need to count how many times "AA" shows up in each Area.


I have attached an example file, with example date.

Thank you all for your help in advance, you guys are so wonderful!

P.S. One of my coworkers suggested this might be done better in MS Access, but unfortunately I don't know ANYTHING about Access, so I’m hoping this can be done in Excel because I'm much more familiar with that program.

My idea would be to append columns to your database labels with each service code... then apply formula to count the number of times each code appears in each row..then base Pivot table on those.

For example

Label P1 as "AA" (without quotes) and Q1 as "XW" and so on

Then in P2 use formula:

=COUNTIF($D2:$O2,P$1)

copied down the database and over as many columns as you have unique codes.

Then drag those to the pivot table and sum the numbers up.

Sorry, didn't think about that and didn't test...

Try this formula instead:

=SUMPRODUCT(--($D2:$O2=P$1))

copied down and across.