Most recent year in COUNTIFS

[DarkLord76865]

I have formula to count how many times does the text repeats in 2017. year:

=COUNTIFS(A3:A53; ">=1.1.2017"; A3:A53; "<=31.12.2017"; B3:B53; "text1")

What I want is formula to count how many times does the text repeats in most recent year (now it is 2018.)
Regards

[Phuocam]

Try this:

=COUNTIFS(A3:A53, ">="&DATE(YEAR(TODAY()),1,1), A3:A53, "<="&DATE(YEAR(TODAY()),12,31), B3:B53, "text1")

Or:

=SUMPRODUCT((YEAR(A3:A53)=YEAR(TODAY()))*(B3:B53= "text1"))

[DarkLord76865]

Try this:

=COUNTIFS(A3:A53, ">="&DATE(YEAR(TODAY()),1,1), A3:A53, "<="&DATE(YEAR(TODAY()),12,31), B3:B53, "text1")

Or:

=SUMPRODUCT((YEAR(A3:A53)=YEAR(TODAY()))*(B3:B53= "text1"))

Just tried first formula you suggested and it works, but now I have different question.
I need formula that will do everything the same as formula you wrote, except it should count how many times does "text1" appears in year that was before most recent year.

[Phuocam]

=SUMPRODUCT((YEAR(A3:A53)=YEAR(TODAY()-1))*(B3:B53= "text1"))

[Roel Jongman]

Can I suggest an intermediate solution.. of putting current year in a cell lets say for the sake of the example in "B1"

Please Login or Register  to view this content.

it is my experience that in may no one thinks about coming in the the office on january 2nd, 2019 to build the report for the whole year. Only to find out that becasue the today formula is in formula that to the logic of the formula excel considers 2018 to be last year and but we humans need more time to finish up last years reporting so we want to still see 2018 as the current year in roughly the fist 2 weeks of january.
The formula given before was fine.. but if you need to look back at last year early in the new year you might want to avoid using today() in the formula. and the separate cell gives you the option.

[DarkLord76865]

=SUMPRODUCT((YEAR(A3:A53)=YEAR(TODAY()-1))*(B3:B53= "text1"))

Thanks for trying to help but this doesn't work for me.
I tried this formula and it just counts how many times "text1" appears in most recent year (2018.).
If you know any other ways to do that I would appreciate your help.

[DarkLord76865]

Can I suggest an intermediate solution.. of putting current year in a cell lets say for the sake of the example in "B1"

Please Login or Register  to view this content.

it is my experience that in may no one thinks about coming in the the office on january 2nd, 2019 to build the report for the whole year. Only to find out that becasue the today formula is in formula that to the logic of the formula excel considers 2018 to be last year and but we humans need more time to finish up last years reporting so we want to still see 2018 as the current year in roughly the fist 2 weeks of january.
The formula given before was fine.. but if you need to look back at last year early in the new year you might want to avoid using today() in the formula. and the separate cell gives you the option.

Thanks on trying to help but unfortunately this is not what I am looking for. I don't want to use more than one cell.

[JeteMc]

Note: Please use the Quick Reply for responding as opposed to 'Reply With Quote' unless there is some confusion as to whom or what point you are speaking.
I believe that if Phuocam's formula is modified as follows it will work:

Please Login or Register  to view this content.

Let us know if you have any questions.

[DarkLord76865]

Thanks on help!!
This is working.
Regards

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.