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?

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!