NESTED DISTINCT COUNTIF FORMULA?

Sarah_lee123

I have another complex situation

Now that I figured out how to "batch" items. The next thing I need to do is pull in a list of "Tote Numbers" that are associated with that batch. In this case I DO want the list to be within cell, but I also want it to be based on the batch number.

here is a flow chart of the system - many plants" per "tote", and multiple "totes" per "harvest batch"

In the source sheet, I have the individual plant tags, harvest batch ID, and tote numbers.

I used a DISTICT/INDEX formua to pull in the Harvest batch name once from the source sheet where it is listed many times.

Now I am trying to count the number of totes per batch

=COUNTIF

how many different tote numbers are there associated with each batch?

Repeating this image from above - you can see that many plants go into tote #01, many other plants go into tote #2, and another set of many plants go into tote #3. All three totes are part of "Harvest batch" GEL.F0029. In this senario, the answer to my formula should be the number three, because three totes comprise one harvest batch, made up of many plants.

I am trying to count the number of totes, even though the tote number is repeated multiple times, because I am listing the tote number for each plant harvested and each tote holds multiple plants.

How do I ask the data to understand that I want it to count each distinct tote number, rather than the numner of times the tote was listed? Must be associate with Harvest Batch Name in the receiving formula sheet?

I hope all that makes sense. I am also trying to see if I can make this set up less complex, but I haven't figured out how to do that yet.

Thanks for your help

Jeff Reisman

@Sarah_lee123

I have worked on similar types of data extractions. It's going to take some creativity, for sure. In my situation, I needed to pull data about various materials from three different warehouses, and rank values for the same materials based on their warehouse. So sort of like your Tote # issue.

One thing I did was create a helper column where I made distinct values from my material names and what warehouse they were in. For example, Material CMB-Y100C was in warehouse 4000 and warehouse 4200. So in my helper column, I had 4000-CMB-Y100C and 4200-CMB-Y100C. Then when I counted the distinct from the helper column, I could determine that CMB-Y100C was in two different warehouses. So you could do the same with your Tote# and Harvest batch ID: 01-GEL.F0029, 02-GEL.F0029, etc. Count the distinct where [Harvest Batch Name]@row = RIGHT({UniqueToteAndBatch}, 9)