COUNT with Distinct with multiple criteria


I am trying to create a count when 2 different criteria, when combined, are unique.

Here is some sample data:

I am trying to count late bills (checkbox) only when the customer number and bill date combined are unique

In a summary field, I am writing this:

=COUNT(DISTINCT(COLLECT([Customer Number]1:[Customer Number]6, Late1:Late6, @cell = true, [Bill Date]1:[Bill Date]6, Late1:Late6, @cell = true)))

My result: 1

My expectation: 6

When I put in a helper column and join the customer number with the bill date, then count, I get 6, as expected.

Unfortunately, because of the size of the sheet, I have to be very stingy with my column and cell count, so I'm trying to avoid the helper column. Is there a way to write a formula that will give me the expected result without the helper column?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!