COUNTIF to count long text/values in cells (#VALUE! Error)

[FrancisSIP]

Hi there,

I have this Excel sheet that groups multiple same items from the first column into one on the last column.



The grouping variables are:

• Same Cost Code (Column A)
• Same Description (Column C)

Will group the items together as 1 item.

The issue I'm having in the grouping process is that the Unique Quantity value (column J) comes out as a #VALUE! error due to Description (Column C) being excessively long. But is fine in some cases like in H50! We would like to retain the values in Description for quoting purposes.

I've tried changing the format of the cells from General to Text but does nothing.

Is there a work-around solution to this? Maybe have Unique Quantity pick a different set of variables? I don't know.

Thanks heaps in advance!

See the Excel sheet attached, and have highlighted the problematic bits in question.