COUNTIFS for specific years

regina.quality

Hi, Im working on a countifs formula i which I would like to get the sum values for both 2024 and 2025. Below is my original formula for 2025… and now, I would like the same formula to also capture 2024.. Thanks in advance for the help! 😊

=COUNTIFS({MMR EQSW Function/Project Date Requested}, ISDATE(@cell ), {MMR EQSW Function/Project Date Requested}, YEAR(@cell ) = 2025, {MMR EQSW Function/Project Code}, "EP", {MMR Engineering Function/Project Status}, "Complete")

Jeremy_D

Hi @regina.quality I think to do that you'll have to add the formula you have to the same formula but with 2024. So if your formula works then it'll be:

=COUNTIFS({MMR EQSW Function/Project Date Requested}, ISDATE(@cell ), {MMR EQSW Function/Project Date Requested}, YEAR(@cell ) = 2025, {MMR EQSW Function/Project Code}, "EP", {MMR Engineering Function/Project Status}, "Complete") + COUNTIFS({MMR EQSW Function/Project Date Requested}, ISDATE(@cell ), {MMR EQSW Function/Project Date Requested}, YEAR(@cell ) = 2024, {MMR EQSW Function/Project Code}, "EP", {MMR Engineering Function/Project Status}, "Complete")

I know it's quite wordy, there may be a better way but this will work.

regina.quality

@Jeremy_D Hi Jeremy, sorry for the late feedback. Much appreciate your advise/suggestion. And yes, it did work!

Jeremy_D

Hi @regina.quality I think to do that you'll have to add the formula you have to the same formula but with 2024. So if your formula works then it'll be:

=COUNTIFS({MMR EQSW Function/Project Date Requested}, ISDATE(@cell ), {MMR EQSW Function/Project Date Requested}, YEAR(@cell ) = 2025, {MMR EQSW Function/Project Code}, "EP", {MMR Engineering Function/Project Status}, "Complete") + COUNTIFS({MMR EQSW Function/Project Date Requested}, ISDATE(@cell ), {MMR EQSW Function/Project Date Requested}, YEAR(@cell ) = 2024, {MMR EQSW Function/Project Code}, "EP", {MMR Engineering Function/Project Status}, "Complete")

I know it's quite wordy, there may be a better way but this will work.

regina.quality

@Jeremy_D Hi Jeremy, sorry for the late feedback. Much appreciate your advise/suggestion. And yes, it did work!

Paul Newcome

You can also shorten this a bit by using an OR function and using an IFERROR for the YEAR function to remove the need for the ISDATE piece.

=COUNTIFS({Date Requested}, OR(IFERROR(YEAR(@cell), 0) = 2024, IFERROR(YEAR(@cell), 0) = 2025), {Project Code}, "EP", {Project Status}, "Complete")

Note: I shortened the {Cross Sheet Reference} names so that the overall syntax was a little easier to read. If you update each of those to the appropriate names, the above should output the same results with a little more efficiency.