Copy from one sheet to other - only listed data

Perhaps not a good title but I'm not sure how to phrase it. I'm a rookie Excel user as you'll tell.

This report will be used to total the number of tickets to our service desk.

I have a sheet with the below columns subtotaled:
Category - Subcategory - Problem - Product - Resolution Analysis Code - Root Cause Breakdown

There are 5 categories, about 28 subcategories, tons of problems, even more products and about 28 RAC's.

The way I have them totaled now is using the below lookup:
=VLOOKUP("HARDWARE COUNT",EITS_CAT_Count,2,FALSE)

HARDWARE COUNT - Hardware is one of the categories - EITS_CAT_Count is the defined name on the "Counts" sheet that is subtotaled.

So, for each category (and every other column) I have listed the categories (Hardware, Software, Telcom etc.) and then a VLOOKUP to list the total number.

The list is going to be very, very long with most of the totals being #N/A since a lot of them aren't listed. I'm wondering how I can only list values that are present in the corresponding "Counts" Sheet?

***in the attachment I've removed the subtotals - in the real one I subtotal each column***

Any help will be appreciated.

Try the following in B3 as an example and then copy down

Please Login or Register  to view this content.

The ISNA function checks if the argument gives a #N/A value and gives an empty string "" if true. Otherwise you get the value given by the VLOOKUP function.

I've also made use of the UPPER function and concatenation character & to give a generic lookup criteria.

Hope this helps.

I uploaded a revised attachment that is accurate and shows what I would like to see.

I would like this to go to the "Counts" sheet and populate the Category (Hardware) in one column and the total (1) in the next column. So, in the attached case, for the totals sheet i

Maybe I didn't put your formula in properly.

I appreciate the help.