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
-
I can't think of anything without the helper column.
-
Thanks, Paul, that's what I was afraid of. I am having a ton of trouble with Datashuttle on larger sheets, so I'm trying to use formulas to reduce column count. I'm having shuttle workflows fail when I only have 100K cells, so I'm trying to shrink things as much as possible.
If you think of anything else, let me know!
-
How many columns o you currently have? What is the error message you are getting?
-
I have between 40-50 columns (happening on a couple of sheets) and a few thousand rows. My cell counts are in the 100K to 200K range, depending on the sheet. Basically, Datashuttle just times out (although it seems to upload some of the data). I have an open ticket with support, but their recommendation after troubleshoot is "use smaller sheets". So, I'm trying to remove helper columns where I can.
-
Hmm... I haven't run into that one before. Are you using any expressions, or are all calculations done on the sheet itself?
-
All calculations are in the sheet itself. I have a couple of filters to prevent extraneous rows, but no expressions.
-
And I guess it would not work for you to use less rows?
-
Regrettably, no. I've already split it into 3 sheets to try to get the row count down, and it is a nightmare with automations and metrics to make sure nothing gets missed. I do have a ticket escalated with support, so fingers crossed they can work out the issues.
-
Unfortunately I am at a loss.
@Andrée Starå Have you run into this before, or do we need to go through support?
-
So, I had a great call with a very knowledgeable Tier 2 tech. Regarding the Datashuttle issues. It does relate to this original thread in that he confirmed that the issue is the complexity of the target sheet- it has too many formulas on it. Currently, 22 columns are formula-based. Essentially, what is happening is that the upload is being slowed down as the system tries to run the calculations, then times out. Sometimes it gets thru, sometimes it doesn't- there does not seem to be a predictable threshold.
So, my options are:
1) Reduce the overall number of formulas in the sheet (fewer, more complex formulas is better than more, simpler formulas, so helper columns are discouraged)
2) Off-load the calculations to a secondary sheet to reduce the complexity of the the datashuttle target sheet. There are multiple problems with this- I may actually start a different thread because it is unique enough.
3) Use multiple datashuttle workflows across multiple similar sheets- I'm trying to consolidate sheets, so this would be a full reversal of the work I've already done.
-
If anyone is following this thread and wants to follow the discussion about Data Shuttle and large sheets, I posted that question here:
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!