excel 2010

it depends on how the data is laid out - here's two examples, one with a pivot table one with formulas based on a couple of guesses as to layout - if these are no good then some sample data would help.

The pivot table is pretty much default (at least in 2013) the sumifs uses formulas
monitors.xlsx

this is what the data looks like - also I had it as a table, but changed it so it is NOT in a table

Department      Old Computer Name      New Computer Name      Monitor
NAD      D01NADD020      D012EADS0004A      HP L1706
2CON      D01ADMD004      D01ADMDS2001A      HP L1702
BIO      D01BIOD008      D01BIODS0001A      SDM-S73
BIO      D01BIOD007      D01BIODS0002A      DELL 1905P
BIO      D01BIOD002      D01BIODS0003A      HP L1710
BIO      D01BIOD010      D01BIODS0004A      HP 1702
BIO      D01BIOD004      D01BIODS0005A      DELL 1708FP
BIO      D01BIOD011      D01BIODS0006A      
CAM      D01CAMW003      D01CAMCS0001A      HP LE2201W
CAM      D01CAMW005      D01CAMCS0002A      HP LE2201W
CAM      D01CAMW006      D01CAMCS0003A      HP LE2201W
CAM      D01CAMW002      D01CAMCS0005A      HP LE2201W

try the attached

I suspect some of your problems might stem from there being a Char(160) at the beginning od the monitor names so I had to manipulate the data a little. If this is close to what you want let me know and I'll re-jig to cope with the non-ascii char if needed
Copy-of-monitors.xlsx

regmigrant - I like how you sorted the data. how did you do this? did you use a pivot table?

cut and paste from ee to excel. Then data tab, convert text to table, using space as a delimiter, corrected the headings (new<>Computer<>Name etc). then when I did the monitor column I noticed that the 'HP' etc were indented but when I tried to reformat to remove the left indent it stayed. So I used "=code(left(a14,1))" in helper cell and saw that it returned 160 which is non-ascii so I used "=substitute(a14,char(160),"") to replace them (in a separate block) then 'cut and paste values' to put them back in your table.

Added a column that combined the manufacturer with model number to get back to a combined name then copied THAT  to a separate block and 'data, remove duplicates' to get the unique names, copy that range and paste special, transpose to get a set of columns headed by the monitor names. Did the same (remove duplicates) to get a unique list of departments  then put the countif in the first intersection and copied across and down. - simples :)

I considered a pivot table but it would need a helper column with a countif anyway because pivots are only good at analysing values and with only two dimensions to the data (dept, monitor) it's unnecessary

Wow! you know Excel. Did you pick this stuff up over time, or take a class? I've always wanted to

learn about Excel because it seems so powerful.

Also, I take it I have add columns like you did.

"Added a column that combined the manufacturer with model number to get back to a combined name"

do

you just copy the data from the manufacturer and then the model number into one column? or is there

some fancy Excel thing that does this?

thank you for the help