COUNTIF or COUNTIFS - Count specific criteria in the current month

Options
✭✭✭✭

I'm trying to count all the P2's that occurred in the current month without having to change my formula every month. I have to change the "05" for May to the corresponding month each month. Here is my current formula:

=COUNTIFS({2020 from Ethica Date}, LEFT(@cell, 2) = "05", {Priority}, "P2")

I use the same formula for the previous month and change the "05" to "04" so I can capture April. There are multiple priorities so it is time consuming and I really want this somewhat automated.

I use this formula on another page that is similar but I don't need to break it down by priority:

=COUNTIF({Month To}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()))

I feel like I have searched all over this forum looking for the answer and I'm certain it's out there, I just can't figure it out. Please help!!

Thanks,

Sheryl Roland

• ✭✭✭✭✭✭
Options

Current Month:

=COUNTIFS({2020 from Ethica Date}, LEFT(@cell, 2) = "05", {Priority}, "P2", {Month To}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()))

Previous Month (accounts for Dec when current month is Jan):

=COUNTIFS({2020 from Ethica Date}, LEFT(@cell, 2) = "05", {Priority}, "P2", {Month To}, IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1))

• ✭✭✭✭
Options

I'm trying to get rid of this LEFT(@cell, 2) = "05", so I don't have to manually change the "05" each month to the corresponding month. I tried the formula above and leaving out the LEFT(@cell, 2) = "05", but I get "Incorrect Argument Set"

• ✭✭✭✭
Options

This formula worked!! I reversed the order of Priority and Date.

=COUNTIFS({Priority}, "P2", {2020 from Ethica Date}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()))

• ✭✭✭✭
Options

Thanks so much for your help!

• ✭✭✭✭✭✭
Options

Happy to help! 👍️

• ✭✭✭✭✭✭
Options

@Paul Newcome having an issue I have a formula and it continues to return 0

=COUNTIFS([Name]:[Name], "XXX", [Name]:[Name], "YYY")

I know that's not correct how can the count function count X & Y

Senior Program Coordinator

De Anza College

• ✭✭✭✭✭✭
edited 06/24/20
Options

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭
Options

Your formula is counting at the moment all occurrences where a cell in the Name cloumn hold the value XXX AND the value YYY at the same time. That's why you get a 0.

You probably want to count cases where the value is XXX OR YYY - this would be the formula:

=COUNTIF([Name]:[Name], "XXX") + COUNTIF([Name]:[Name], "YYY")

• ✭✭✭✭✭✭
Options

@Stacey Carrasco Werner is correct. Your formula is looking for a cell that contains two separate values at the same time which isn't possible.

You can either use Werner's solution or if you have other criteria you would like to eventually work into the COUNTIFS you can use an OR statement like so:

=COUNTIFS(Name:Name, OR(@cell = "XXX", @cell = "YYY"))

Using the OR will allow you to work in more range/criteria sets or additional names without having to update multiple COUNTIFS statements.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!