Attempt to Count Unique Values, Multiplied by Quantity

Originally Posted by HENRYR.

which is shown on tab "DOOR QUANTITY AND DESIGN".

Nothing titled DOOR QUANTITY AND DESIGN, do you mean the sheet UNIT DOOR COUNT TAKEOFF?

Originally Posted by HENRYR.

These are noted in columns V and W.

Which sheet?

Originally Posted by HENRYR.

Attachment 862069

I am attempting to count all unique values on a table (and those values are text), which is shown on tab "DOOR QUANTITY AND DESIGN". These are noted in columns V and W.

While I would like to use a UNIQE function or a COUNTIF function, I also have to factor in the quantity of each door, noted in Column A.

For context: the Enclosed Spreadsheet is a quantity takeoff of door hardware, so with each unique door there could be multiple different peices of hardware.

I don't know how to condense this information to get a unique count of text displayed on the table while also multipling the overall quantity from column A.

Please give a clear range, do you mean like the range b6:b12 in sheet UNIT DOOR COUNT TAKEOFF.
I am afraid that I do not follow.

There is no such thing as Excel 2020. Which Excel product are you suing (Office 365, Excel 2021, Excel 2019, etc)?

Yes. Please modify your profile to show O365, and not Excel 2020.

OK. I think there is something missing from your description. You said:

Why are these two included in the total, when they are different:

YALE COLLECTION, VALDOSTA, NO. 21 - PRIVACY. FINISH - 625 - BRIGHT CHROME PLATED.
and

YALE COLLECTION, VALDOSTA, NO. 11 - PRIVACY. FINISH - 625 - BRIGHT CHROME PLATED.

Unfortunately, Excel is very unforgiving regarding typos. There are two typos in the sheet, that are messing things up.

1. On another sheet, in an empty cell:

Formula:

Please Login or Register  to view this content.

You will note that two items are duplicated, at rows 9 & 10. I traced these down to rows 174 and 175 of your raw data. On ALL the other entries for that item it reads:

"YALE COLLECTION, VALDOSTA, NO. 11 - PASSAGE. FINISH - 625 - BRIGHT CHROME PLATED. "

for those two it reads:

"YALE COLLECTION, VALDOSTA, NO. 11 - PASSAGE. FINISH - 625 - BRIGHT CHROME PLATED."

The difference is that all the others have an unnecessary trailing space at the end.

Three choices.

1. Manually copy/paste to get rid of the trailing spaces (probably the best way)
2. Use data validation to ensure consistency in the first place.
3. A fix, but my LEAST preferred method as it papers over the cracks... amend the formula to read:

Formula:

Please Login or Register  to view this content.

One word of advice. Samples are BEST if they contain 10-20 rows MAXIMUM and you provide expected results. Your sheet was larger and the expected results were NOT reflective of the raw data... unless I completely misunderstood the question!! That is always a possibility...