Mar 28 2023 11:02 AM
Hello! I have a table with a list of transactions by date. I would like to count the number of times a phrase appears in a particular year. How many times did we list "General Operating Support" in 2011. I think I could do this with COUNTIFS but I must be getting the syntax wrong. Please see attached for a sample of the data and my formula I tried to use, which was =COUNTIFS(A:A,year=2011,B:B,"General Operating Support").
Mar 28 2023 11:14 AM
=SUMPRODUCT((YEAR($A$2:$A$34)=E2)*($B$2:$B$34="General Operating Support"))
Does this return the expected result?
Mar 28 2023 11:35 AM
I know there's probably a more elegant solution, especially if you are using the most current version of Excel, but I just added a helper column that contains this formula (copied down to all rows)
=YEAR(A2)
so that there's a single column that is only the year,
Then this formula gets your answers (with the flexibility of changing the year in cell E2
=COUNTIFS(C:C,E2,B:B,"General Operating Support")
See the attached.
Mar 28 2023 11:38 AM
And yet another solution, one that requires a very current version of Excel
=COUNT(FILTER(A2:B34,(YEAR(A2:A34)=E2)*(B2:B34="General Operating Support")))
Mar 28 2023 12:01 PM
Mar 28 2023 12:08 PM
Mar 28 2023 12:09 PM
No, but, again if you have a current version of Excel, this formula, with FILTER at the heart of it, does work.
=COUNT(FILTER(A2:B34,(YEAR(A2:A34)=E2)*(B2:B34<>"General Operating Support")))
Here's a reference to help you understand FILTER, a very useful function in many circumstances.
Mar 28 2023 12:25 PM - edited Mar 28 2023 12:29 PM
If you want to stick with COUNTIFS, the following should work:
=COUNTIFS(A:A,"<01/01/2012",A:A,">31/12/2010",B:B,"General Operating Support")
NB! My date format is dd/mm/yyyy, you will need to tweak this for your date format.