Counting Distinct ID that excludes certain business titles and includes Open Status only

Julius Wilson

Hi can someone help me out with this one,

=COUNTIFS(DISTINCT({Requisition ID}, {Business Title}, NOT(OR(@cell <> "MSS", @cell <> "Sales Engineering"))))

I want to return the count of distinct req IDs, that are open, but exclude business titles that contain "MSS" or "Sales Engineering".

I have tried a few things but no luck

Genevieve P.

Hi @Julius Wilson

You're close! The <> operator says "does not equal" so you don't need to use the NOT function as well.

Try using the combination of COUNT(DISTINCT(COLLECT( as well.

The COLLECT Function will filter down your columns by your criteria, the DISTINCT will only surface the unique values, and then the final COUNT will return the number of distinct rows.

Finally, you'll want to use AND instead of OR.

Try this:

=COUNT(DISTINCT(COLLECT({Requisition ID}, {Business Title}, AND(@cell <> "MSS", @cell <> "Sales Engineering")))

Cheers,

Genevieve

Julius Wilson

Hi Genevieve, Thank you I did try the Count, countif and countifs functions along with the collect and the AND but they are only returning the value of 1. (I tried a few combinations of things :) )

What I ended up doing in the short term was exporting the rows I wanted, excluding the ones I did not and then just doing a DISTINCT on the Req ID on that new sheet. But, would have been so nice to be able to figure this one out.

Thanks Again.

Genevieve P.

Hi @Julius Wilson

Would you be able to provide a screen capture of the source sheet (but block out sensitive data)? How is your {Business Title} column formatted (and what type of column is it)?

Julius Wilson

Hi Genevieve, I want to add a part that only looks at the open Reqs also. But figure let me break it down into a small size first and get that working. The Business Title field is dropdown so the recruiters will pick for consistency. the Primary Column is the Req ID

Genevieve P.

Hi @Julius Wilson

Thank you for this additional information! I wonder if it may have to do with the two criteria we're trying to exclude from that Business Title column.

Can we actually first try the other way around, Distinct and Open?

=COUNT(DISTINCT(COLLECT({Requisition ID}, {Status}, "Open")))

If this is correct, lets add on one of the elements to exclude:

=COUNT(DISTINCT(COLLECT({Requisition ID}, {Status}, "Open", {Business Title}, <> "MSS")))

Note that this will only exclude cells that exactly say "MSS" in them, versus anything that contains MSS, like "MSS - Title".

Do either of these work?

Julius Wilson

Hi Genevieve this part works

=COUNT(DISTINCT(COLLECT({Requisition ID}, {Status}, "Open")))

Once I add the {Business Title} Part it still returned 1 no matter what business title I added.

Thank You, Part of it is now figured out :)

Genevieve P.

Hi @Julius Wilson

This is really good to know! We're narrowing it down. Ok, would you mind trying two things?

1 - Can you try doing a COUNT of <> "MSS"? This should bring back a really high number but I'm curious if this just brings back 1.

Try:

=COUNTIF({Business Title}, <> "MSS")

and

=COUNTIF({Business Title}, @cell <> "MSS") to see if that makes a difference

Can you also check the {Business Title} cross-sheet reference to make sure it's looking at the correct column?

2 - Instead of excluding "MSS", what if we tried including cells that contained the other values you're looking for.

ex:

=COUNT(DISTINCT(COLLECT({Requisition ID}, {Status}, "Open", {Business Title}, CONTAINS("MAE", @cell))))

Let me know if any of this has helped!

Cheers,

Genevieve

Julius Wilson

Hi Genevieve, thank you I will try these and let you know.

Julius Wilson

Hi Genevieve,

1. =COUNT(DISTINCT(COLLECT({Requisition ID}, {Status}, "Open", {Business Title}, <> "MSS"))) I tried this again and is seems to do the trick this time. (I am checking with excel to make sure get the same answer)

What I noticed was it I add a space in the front of the ex: "(Space)MSS" I had extra counts and not exact with what I was seeing in excel. Maybe that's why wasn't working correctly for me yesterday.

Also based on the above this will calculate anything that contains "MSS" or does it have to be the exact title?

2. =COUNT(DISTINCT(COLLECT({Requisition ID}, {Status}, "Open", {Business Title}, CONTAINS("MAE", @cell)))) This is working correctly as well for the business title entered or a Word contained in title.

How would I add multiple business titles to the two solutions above? Will I need to change to COUNTIF or COUNTIFS ? Or add an additional {Business Title}, CONTAINS()?

=COUNTIF({Business Title}, @cell <> "Dir, MSS Enterprise") This seems to calculate correctly what is there excluding the business title entered.

Thank You,

Julius Wilson

Genevieve P.

Hi @Julius Wilson

1 - This operator <> says "not", so the Count will return everything that is not "MSS", is that what you were looking to do?

We did not use the CONTAINS function in this formula so it will only skip cells where "MSS" is specifically stated, without any other content in that same cell. Did you want to adjust this to exclude cells that contain "MSS"?

In testing, I realized that CONTAINS actually prefers us to use the NOT function instead of <>

Try this:

=COUNT(DISTINCT(COLLECT({Requisition ID}, {Status}, "Open", {Business Title}, NOT(CONTAINS("MSS", @cell)))))

To add a second criteria when excluding a value, you would list {Business Title} and your additional statement again:

=COUNT(DISTINCT(COLLECT({Requisition ID}, {Status}, "Open", {Business Title}, NOT(CONTAINS("MSS", @cell)),  {Business Title}, NOT(CONTAINS("Other", @cell)))))

2 - I'm glad you were able to get this one working!

To add two separate COUNTIFS together, you'll want to write the formula twice with a + in between, like so:

=COUNT(DISTINCT(COLLECT({Requisition ID}, {Status}, "Open", {Business Title}, CONTAINS("MAE", @cell)))) + COUNT(DISTINCT(COLLECT({Requisition ID}, {Status}, "Open", {Business Title}, CONTAINS("MSS", @cell))))

Let me know if this has helped.

Cheers,

Genevieve

Julius Wilson

Thank You Genevieve, I was out for a few weeks. I appreciate the feedback. I will test out what you recommended and let you know.

Julius Wilson

Julius Wilson

HI Genevieve, that you these seem to be working, initially they were not returning any value except for 1, I am not sure if it was a refresh or save , or delay in communication between the two sheets. But, the formula are working for multiple selections now.

Thank You

Genevieve P.

I'm glad to hear it's working now!