Forum Discussion
Help with COUNTIFS
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").
7 Replies
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.
- You can also use..
=COUNTIFS(A2:A34,">=01-01-"&E2,A2:A34,"<=31-12-"&E2,B2:B34,"*General Operating Support*")
Advice: don't use array like A:A. Instead used actual length like A2:A34. The best is to convert the range into a table and then use array like tablename[columnname].So if the table name is transactions then array for A:A column would be transactions[Payment Date]..and so so. Table also includes row automatically when added. It also automatically copies formula to all rows below. 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")))
- So it looks like there might just be something wrong with the way I have my date fields formatted. When I use mathetes idea of pulling out the Year into a separate column, my COUNTIFS idea worked fine. Now I also need to display the number of records that are NOT General Operating Support. I thought I could do
=countifs(A:A,E2,B:B,<>"General Operating Support")
but Excel doesn't like that.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.
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.
=SUMPRODUCT((YEAR($A$2:$A$34)=E2)*($B$2:$B$34="General Operating Support"))
Does this return the expected result?
