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
Check out the Formula Handbook template!