Help with COUNTIFS

Copper Contributor

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

@Rebekah110 

=SUMPRODUCT((YEAR($A$2:$A$34)=E2)*($B$2:$B$34="General Operating Support"))

 

Does this return the expected result?

@Rebekah110 

 

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.

@Rebekah110 

 

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

@Rebekah110 

 

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.

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.