Trying to only pull the month from a cell if the a certain drop box is picked

A_Dobson

I am trying to count the amount of projects in a month with the date entered but only pull it out if a the drop down box says archived, I believe the month count is correct as that part alone works but when trying to add the code for the dropdown box it does not work, any help would be apricated, code below;

=COUNTIFS({Project Quality Engineer Project Updates Range 8}, <=DATE(2022, 3, 31)) - COUNTIF({Project Quality Engineer Project Updates Range 8}, <=DATE(2022, 3, 1)) COUNTIFS({Project Quality Engineer Project Updates Range 5}, "archived")

Paul Newcome

Try this instead:

=COUNTIFS({Project Quality Engineer Project Updates Range 8}, AND(IFERROR(MONTH(@cell), 0) = 3, IFERROR(YEAR(@cell), 0) = 2022), {Project Quality Engineer Project Updates Range 5}, @cell = "Archived")

NOTE: I adjusted the overall formula so that you only have a single COUNTIFS to save a little bit of typing and some back end efficiency.

A_Dobson

Perfect, thanks for your help

Adam

A_Dobson

Is there a quick way to do all except one item in the dropdown box. for example, all except Archived

Andrée Starå

Hi @A_Dobson

I hope you're well and safe!

Try something like this.

=COUNTIFS({Project Quality Engineer Project Updates Range 8}, AND(IFERROR(MONTH(@cell), 0) = 3, IFERROR(YEAR(@cell), 0) = 2022), {Project Quality Engineer Project Updates Range 5}, @cell <> "Archived")

Did that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!

Paul Newcome

@A_Dobson You would switch the argument from equals (=) to not equal to (<>).

=COUNTIFS({Project Quality Engineer Project Updates Range 8}, AND(IFERROR(MONTH(@cell), 0) = 3, IFERROR(YEAR(@cell), 0) = 2022), {Project Quality Engineer Project Updates Range 5}, @cell <> "Archived")