Countifs Formula assistance

[saiyawil]

Hello all,

I have a question regarding the countifs formula. In my file, I would like to count the number of different types of furniture on each floor. I have a main inventory worksheet with all the data represented with references (i.e D1 for desk workstation), a 'key' worksheet listing all my references for each type of furniture, and a 'summary table' worksheet to represent the data neatly.

The problem that I have is counting cabinets because I need to be specific for low and mid ones. Unfortunately the references for both low cabinet and mid cabinet are the same in the 'key' workbook. Now, I can add more countifs functions to do this (as I did in the workbook) but it can be very tedious. Is there a one step option that adds a 3rd condition to check whether the item counted is a low or mid cabinet? Before, I tried something like =COUNTIFS('Main Inventory'!D:D,C4,'Main Inventory'!F:F,"CAB1",'Key'!B:B,"Mid Height Cabinet", but that is definitely wrong! Attached sample worksheet is below.

Thanks for your help in advance.

Wsp

[XOR LX]

Hi,

Not sure I understand why you've set up your Main Inventory tab the way you have. You have a Unique Key Ref (actually, there are 4 duplicates in here, so it's not quite "unique") in your Key tab - would it not make sense to have this listed in the Main Inventory tab also?

Regards

[saiyawil]

Hi XOR,

Yes I'm still fixing up the data, but there is about 25,000 data listings in the full main inventory sheet that I didn't upload and I figured it would be best to have a "key" to reference the data to instead.

Wsp

[amy_d2]

Hi

i think you should take vlookup of item type in summary sheet based on unique key ref (after removing duplicate key reference or make them correct) & use countifs

[XOR LX]

"Yes I'm still fixing up the data, but there is about 25,000 data listings in the full main inventory sheet that I didn't upload and I figured it would be best to have a "key" to reference the data to instead."

But why? If you already have a unique identifier, then that is all you require to ensure any lookups to/searches within that data will be correct. I appreciate the idea behind it, but I feel that you're making the formula-work unnecessarily complicated and strongly advise that you retain the unique identifiers in your main data tab.

Regards