Formula for COUNTIFS function with multiple criteria

Clare W

Looking for help with my COUNTIFS formula for my sheet summary... I am trying to count all instances of the value "Mega" in the "Deal Size/SET Level of Support" column when the "Status:" column is any of the following "In Progress", "Proposal Presented", " Proposal Presented & Shortlisted to Presentation Stage", "Presentation Stage". I'm receiving an #INCORRECT ARGUMENT SET error message when I use the formula I tried to create below:

=COUNTIFS([Deal Size/SET Level of Support]:[Deal Size/SET Level of Support], "Mega", [Status:]:[Status:], CONTAINS(@cell = "In Progress", @cell = "Proposal Presented", @cell = "Proposal Presented & Shortlisted to Presentation Stage"))

I've also tried the following formula and it's returning a "0" result but it should really be returning "4" if the formula was correct -

=COUNTIFS([Deal Size/SET Level of Support]:[Deal Size/SET Level of Support], "Mega", [Status:]:[Status:], "In Progress" + COUNTIFS([Deal Size/SET Level of Support]:[Deal Size/SET Level of Support], "Mega", [Status:]:[Status:], "Shortlisted to Presentation Stage" + COUNTIFS([Deal Size/SET Level of Support]:[Deal Size/SET Level of Support], "Mega", [Status:]:[Status:], "Proposal Presented" + COUNTIFS([Deal Size/SET Level of Support]:[Deal Size/SET Level of Support], "Mega", [Status:]:[Status:], "Proposal & Presentation Presented"))))

Anyone have any suggestions on what the issue is with my formula or know what the correct formula should be?

Thanks in advance!!

Genevieve P.

Hi @Clare Wieck

Your second formula is the right idea! You just have the closing parentheses in the wrong place. Essentially you'll be adding together 4 completely separate COUNTIFS statements, so they need to close off immediately after the Status criteria.

Try this:

=COUNTIFS([Deal Size/SET Level of Support]:[Deal Size/SET Level of Support], "Mega", [Status:]:[Status:], "In Progress") + COUNTIFS([Deal Size/SET Level of Support]:[Deal Size/SET Level of Support], "Mega", [Status:]:[Status:], "Shortlisted to Presentation Stage") + COUNTIFS([Deal Size/SET Level of Support]:[Deal Size/SET Level of Support], "Mega", [Status:]:[Status:], "Proposal Presented") + COUNTIFS([Deal Size/SET Level of Support]:[Deal Size/SET Level of Support], "Mega", [Status:]:[Status:], "Proposal & Presentation Presented")

If you're still getting 0, you can troubleshoot this by trying each individual statement out to see where the issue may lie:

=COUNTIFS([Deal Size/SET Level of Support]:[Deal Size/SET Level of Support], "Mega", [Status:]:[Status:], "In Progress")

=COUNTIFS([Deal Size/SET Level of Support]:[Deal Size/SET Level of Support], "Mega", [Status:]:[Status:], "Shortlisted to Presentation Stage")

=COUNTIFS([Deal Size/SET Level of Support]:[Deal Size/SET Level of Support], "Mega", [Status:]:[Status:], "Proposal Presented")

=COUNTIFS([Deal Size/SET Level of Support]:[Deal Size/SET Level of Support], "Mega", [Status:]:[Status:], "Proposal & Presentation Presented")

Then check the criteria for the one with the wrong result, since what's "in these" will need to be identical to what's in your sheet.

Let me know if this works for you or if you have any questions!

Cheers,

Genevieve

Stefan

Hi @Clare Wieck and hi @Genevieve P,

great solution by Genevieve, but I think it can be done much easier ;-)

If I'm not totally off track the question is:

COUNT all MEGA in the "Deal Size/SET Level of Support" column

IF

column "Status:" contains one of the above listed status.

Suggestion (the OR needs to be extended with your choice):

=COUNTIFS([Deal Size/SET Level of Support]:[Deal Size/SET Level of Support], "Mega", [Status:]:[Status:], OR(@cell = "In Progress", @cell = "Proposal Presented"))

Hope this helps

Stefan

PS: I assume there can be only one status and your dropdown column therefore is not multi.