Figuring out which function to use! COUNTIFS with HAS

nroth

Hi Community!

Searching for help figuring out which function would be easiest to use for my sheet needs.

Here is a screenshot of my sheet. We have two types of product, singles and panels, and panels are made up of singles.

I want this report to list the panels and singles needed for a specific testing procedure, but I want my list to only contain those that are not already included, regardless of it being a single or panel.

Example: The Chem 17 includes an ALT within the panel, so I don't want the sheet to include any singles of ALT.

I know I must add in a formula to column from the main sheet that the report is based on, I have tried adding in a column and using the COUNTIFS function, but I don't even know if that is the best formula to use! Any advice is highly appreciated!

Dakota Haeffner

I don't think I understand what exactly you are going for here? Are you looking to count the amount of times you seem ATL within that Panel Contents column?

Sorry I reread it and can't seem to get it.

Genevieve P.

Hi @nroth

I'm also having a little trouble understanding the request. Could you perhaps provide a few more examples? Do you have a column for "Singles" to reference as well?

nroth

Hi @Genevieve P. @Dakota Haeffner

Thanks for responding to my question. Hopefully this explanation is a bit more clear:

Here is a screenshot of my main sheet.

Each single and panel has a lot number associated with it. Only certain panels and singles need to be tested on a different platform. On my main sheet, I have a column "Cat One Backcheck?" that is being used to indicate if that lot needs to be tested on the other platform.

I have created a report from this sheet to only list those chemistries that need to be tested on the other platform. Aka only those with the "Cat One Backcheck" as "Yes" on my main sheet.

Once testing is finished, the user will check a checkbox column "Chosen for CatOne BC?" to indicate that specific lot was tested, an example picture is below, as if testing has been completed for Equine15 lot 85479.

This testing only needs to happen once for each chemistry. Meaning now that testing has been done on Equine15 lot 85479, testing does not need to be done on CK lot 89466 because there is a CK within an Equine 15 (you can tell this by the "Panel Contents (Reference)" column.)

I need help creating a formula to remove the single of CK from this report, since we do not need to test it anymore since that Equine15 lot 85479 contains a CK.

I know this is a pretty convoluted problem , but any advice is highly appreciated!!

Genevieve P.

Hey @nroth

Apologies for the delay! It's incredibly helpful to see all this content/explanation.

One thing to consider is that you cannot have a formula in a cell and also manually adjust a cell...meaning we cannot apply a column formula to the "Chosen for CatOne BC" column because you need to manually check it as well.

What you could do is change the "Cat One Backcheck?" formula to return something different if the specific single Panel has been reviewed as part of another check.

For example, you could add this to the front of whatever your formula is in that column:

=IF(AND(Type@row = "Single", COUNTIFS([Chosen for CatOne BC?]:[Chosen for CatOne BC?], 1, [Panel Contents (Reference)]:[Panel Contents (Reference)], HAS(@cell, [Panel Contents (Reference)]@row))) > 0, "Single Checked Already", --- the rest of your formula ---

This will check to see that if the current row is a "Single" row, then it counts how many times this same Panel content (e.g. "CK") appears in the column "Panel Contents (Reference)" where a checkbox has been checked in your "Chosen for CatOne BC?" column.

If there's even 1 row that has that value and a checkbox next to it, then your formula will return "Single Checked Already" which should filter it out of your Report. Does that make sense?

Keep in mind that your single values must be unique as in those letters can't appear within another Panel type, otherwise you may get false positives.

For example, if your Panel is "TP", but there's a different panel called "TPL" then technically your "TP" exists in that column because those letters appear in that cell. To work around this, you could use a Multi Select column instead of that text column. Then the HAS function will look specifically for "TP" as an individual selection within the mutli select column.

Let me know if this makes sense and will work for you!

Cheers,

Genevieve

nroth

@Genevieve P.

Wow! You're a genius.

That worked, but now I have a request to change to report to a sheet.

So its copying rows from my "Build Status Sheet" to a "CatOne Build Status Sheet" if the column "CatOne Backcheck?" is "Yes" AND the column "Chosen for CatOne BC?"

Now I am wondering if I can pretty much keep this same idea, but have it come from my "Build Status Sheet"

The "CatOne Backcheck?" column is populated from my master data sheet. So if I can add to this formula what you have above, it should be able to do the same thing? Return a "Single Checked Already" if it can count it within the panel content reference column?

Thanks so much for your time and patience on this. You have been a huge help! :)

Genevieve P.

Hey @nroth

Were you able to figure this out? You can use two sheets instead of a Report, although I would expect a Report is easier so you don't have duplicate information.

What is your current full formula that's working? We can change the [in sheet references] to be {Cross Sheet References} to check the original sheet for your criteria.