COUNTIF or COUNTIFS - Count specific criteria in the current month

Sheryl Roland

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

Paul Newcome

My apologies.

Current Month:

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

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

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

Paul Newcome

The problem with my initial formulas is that I had initially misread your ranges. Sorry about that.

Paul Newcome

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))

Sheryl Roland

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"

Sheryl Roland

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

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

Paul Newcome

My apologies.

Current Month:

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

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

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

Paul Newcome

The problem with my initial formulas is that I had initially misread your ranges. Sorry about that.

Sheryl Roland

Thanks so much for your help!

Paul Newcome

Happy to help! 👍️

Stacey Carrasco

@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

Andrée Starå

Deleted. Misread the post. 😉

Werner Gerstacker

Hi @Stacey Carrasco ,

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")

Paul Newcome

@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.