Count & Sum Distinct Values based on Criteria | COUNT(DISTINCT(COLLECT(...) | Returning 1 ???

Mike Meyer

In a ROLLUP sheet, referencing a source sheet, I'm trying to count (and sum) DISTINCT values that meet multiple criteria. My formula keeps returning "1", when in the shown scenario below has "80" distinct values. Hoping I'm just missing something simple.

Rollup sheet purpose = count the distinct values rented within individual machine classes.

[Example (shown in the screenshot): YTD there were 270 invoices for machine class = "TELESCOPIC FORKLIFT". Within those 270 invoices, there were only 80 distinct serialized machines. So, I want my formula to produce the "80" distinct serialized machines, but the result of my formula is 1. Some serial#'s found within this machine class "TELESCOPIC FORKLIFT" are numeric, and some are alpha-numeric.]

[Example (shown in the screenshot): YTD there were 446 invoices for machine class = "SCISSOR LIFT". Within those 446 invoices, there were only 119 distinct serialized machines. The formula produced the correct result. ALL serial#'s found within this machine class "SCISSOR LIFT" are numeric only.]

Issue = I have some machine classes where my current formula returns only "1" when I know there are more than 1 distinct serialized machines rented in that machine class.

Notable =

1.) For some of the machine classes my formula produces the correct result, and some do not.

2.) None of the columns in the source sheet are dropdowns.

3.) Within machine classes where the formula works, all machine serial #'s are of consistent format (either the serial # is ALL numbers or ALL serial numbers are alpha-numeric). Whereas the machine classes where the formula does NOT work, the serial #'s are NOT consistently formatted (some serial #'s are all numbers AND some serial #'s are alpha-numeric).

In the subsequent columns ("1", "2", .....), I use the same formula, adding a second criteria to return the count of distinct serialized machines within that month (Jan =1). The formula in those columns present the same issue in the same rows.

Thank you for your help!

Paul Newcome

The problem is the mix of data types in the serial number column. You will need to add a helper column (can be hidden after setting up) that converts every row within that column into text and then reference this in your formula.

=[Serial #]@row + ""

=[Column Name]@row plus quote quote

Paul Newcome

The problem is the mix of data types in the serial number column. You will need to add a helper column (can be hidden after setting up) that converts every row within that column into text and then reference this in your formula.

=[Serial #]@row + ""

=[Column Name]@row plus quote quote

Mike Meyer

@Paul Newcome to the rescue again and FAST! Thank you very much, Paul!

Paul Newcome

Happy to help. 👍️