How to Skip a Criteria with COUNTIFS

Stefano Ferretti

Hi All,

have another query about this formula.

I am counting the number of projects from a different sheet, based on different variables:

1 -Priority

2- Sponsor

3- Status

I have Priority and Sponsors on dropdown cells in the same sheet, so the idea is for the formula to match the Sponsor and the Priority on those cell, and provide back the number of projects that have both the selected sponsor and the selected priority, and then provide a count of those that have status "Yellow"

The formula I have is:

=COUNTIFS({Test Pursuit Sheet - Range 1}, FIND([Priorities]5, @cell) > 0, {Pursuit Sheet - Range 3}, FIND([Sponsor]5, @cell) > 0, {Test Pursuit Sheet - Range 2}, "Yellow")

The formula works like a charm.

I noted though, that if I left one of the 2 drop down column empty it would simply ignore that in the formula, and proceed to count based on only 2 criteria.

So for example if I only select Sponsor A but leave the Priority blank, the formula would give me back ALL the projects with that Sponsor that have status Yellow.

Needless to say I loved this :) as it would allow me to also calculate the 'totals' based only on 1 of the dropdowns.

Unfortunately if I add text to the blank dropdown, then the count will return 0 .

So the question is:

How do I modify the above Formula so that if the 'Priority' I select is not a match it would provide me the all the projects that match the other 2 variables (Sponsor and Status Yellow?)

Sorry, it is a bit wordy....

Ben Goldblatt

Hi Stefano,

If I'm understanding this correctly, the goal of the formula is to populate a count if either the Priority or Sponsor value matches in both the source and destination sheets and the Status is Yellow. If this is the case, you should be able to accomplish this using an OR function within your COUNTIFS formula. I've setup some sheets to test this and here's what my source sheet looks like:

So I have Priority dropdown values of "Pri 1" through "Pri 4" and Sponsor dropdown values of "Sp 1" through "Sp 4". My count for Yellow status rows where either the Priority is "Pri 1" or Sponsor is "Sp 1" should be 4. Using the same reference names you used, my formula looks like this:

=COUNTIFS({Test Pursuit Sheet - Range 2}, "Yellow", {Test Pursuit Sheet - Range 1}, OR(@cell = Priorities1, @cell = ""), {Pursuit Sheet - Range 3}, OR(@cell = Sponsor1, @cell = ""))

 The quotation marks will account for blank cells. So the logic for my destination sheet formula is:

 Generate a count for Yellow status rows where either the "Priority" value is "Pri 1" or the "Sponsor" value is "Sp 1". Here's what my destination sheet looks like using the formula for the other Priority and Sponsor values. Note that while I could use row number references for Priority and Sponsor, I decided to go with @row references as this allows me to use the same exact formula on each row.

Try testing out this formula to see if it works for you. If this isn't what you're looking for, post some screenshots (please make sure to block out or blur any sensitive data) showing what you're working with and I'll be happy to assist further.

Thanks,

Ben

Stefano Ferretti

Hi Ben,

apologies for the delay.

Ok, let me test your formula out.

The 'complication' that I see at first glance is that I have to use the FIND function inside the OR so that the formula will change when selecting a different item on the drop down menu, and will keep on working automatically should the drop down be edited in the future.

I'll be back ;)

Stefano Ferretti

Hey again,

Made some quick tests: as I imagined, one of the limitation of your formula is that, for example, 1 row would have both priority 1 and priority 2, the formula wouldn't catch it.

I tried adding the FIND function, but it doesn't seem to work properly for some reason...

First Screenshot is the 'pursuit sheet' where I have my sponsors, priorities and status.

Using the formula =COUNTIFS({TEST Pursuit Range 1}, "Yellow", {TEST Pursuit Range 2}, OR(FIND(Sponsor1, @cell)), {TEST Pursuit Range 3}, OR(FIND(Priorities1, @cell))) , I have the result below.

Note:

TEST Pursuit Range 1 is the 'status' column on the pursuit sheet

TEST Pursuit Range 2 is the Sponsor columns

TEST Pursuit Range 3 is the Priority column

For some reason the formula works with sponsor 1 and priority 1, doesn't like priority 2 (shouldn't be a type as I copied/pasted the dropdown) and when using "All" which should exclude all the priorities and give me the totals, it return 0