COUNT with Distinct with multiple criteria

AFlint

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?

Paul Newcome

I can't think of anything without the helper column.

AFlint

@Paul Newcome

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!

Paul Newcome

How many columns o you currently have? What is the error message you are getting?

AFlint

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.

Paul Newcome

Hmm... I haven't run into that one before. Are you using any expressions, or are all calculations done on the sheet itself?

AFlint

All calculations are in the sheet itself. I have a couple of filters to prevent extraneous rows, but no expressions.

Paul Newcome

And I guess it would not work for you to use less rows?

AFlint

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.

Paul Newcome

Unfortunately I am at a loss.

@Andrée Starå Have you run into this before, or do we need to go through support?

AFlint

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.

AFlint

If anyone is following this thread and wants to follow the discussion about Data Shuttle and large sheets, I posted that question here:

https://community.smartsheet.com/discussion/85522/breaking-down-large-sheets-to-improve-system-performance