# Excel question - COUNTIF by month? **URL:** \ **Category:** Factual Questions\ **Created:** [October 12, 2011, 11:32am UTC](https://boards.straightdope.com/t/excel-question-countif-by-month/599415 "2011-10-12T11:32:25Z")\ **Posts on this page:** 20\ **Page:** 2
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [October 14, 2011, 10:34am UTC](https://boards.straightdope.com/t/excel-question-countif-by-month/599415/21 "2011-10-14T10:34:55Z")
> [@BubbaDog](#): > > The – is just a list notation for sum product. It’s not just arbitrary notation. The “–” performs a very specific operation that is required for this usage. The expression in SUMPRODUCT (in this example) returns a boolean value (TRUE or FALSE). However, SUMPRODUCT requires an arithmetic expression for this argument. Using “-” in front of a boolean expression forces the conversion to a number; TRUE becomes -1 and FALSE becomes 0. Then you use the second “-” to convert the -1 to 1. ---
**Author:** ![BubbaDog](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/bubbadog/32/333_2.png) [@BubbaDog](https://boards.straightdope.com/u/BubbaDog)\ **Post date:** [October 14, 2011, 1:07pm UTC](https://boards.straightdope.com/t/excel-question-countif-by-month/599415/22 "2011-10-14T13:07:58Z")
> [@CookingWithGas](#): > > It’s not just arbitrary notation. The “–” performs a very specific operation that is required for this usage. The expression in SUMPRODUCT (in this example) returns a boolean value (TRUE or FALSE). However, SUMPRODUCT requires an arithmetic expression for this argument. Using “-” in front of a boolean expression forces the conversion to a number; TRUE becomes -1 and FALSE becomes 0. Then you use the second “-” to convert the -1 to 1. Thanks for the explanation. I “fessed up” to my blunder in post 16 but I didn’t really provide an explanation of why it was necessary as a boolean result modifier. ---
**Author:** ![jjimm](https://avatars.discourse-cdn.com/v4/letter/j/ba8739/32.png) [@jjimm](https://boards.straightdope.com/u/jjimm)\ **Post date:** [October 14, 2011, 1:17pm UTC](https://boards.straightdope.com/t/excel-question-countif-by-month/599415/23 "2011-10-14T13:17:30Z")
> [@CookingWithGas](#): > > The expression in SUMPRODUCT (in this example) returns a boolean value (TRUE or FALSE). However, SUMPRODUCT requires an arithmetic expression for this argument. Except it doesn’t require it - it uses 1 and 0. Or is that what the CTRL-ENTER is for? ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [October 14, 2011, 1:26pm UTC](https://boards.straightdope.com/t/excel-question-countif-by-month/599415/24 "2011-10-14T13:26:10Z")
I posted an explanation based on an earlier post and see that it has already been explained. > [@jjimm](#): > > Except it doesn’t require it - it uses 1 and 0. Or is that what the CTRL-ENTER is for? I have not tested this particular solution but I have written dozens of SUMPRODUCT formulas where it is required. The CTRL-SHIFT-ENTER sequence causes a formula to be interpreted as an _array formula_. The braces around the formula indicate this (you cannot just type the braces in; this is Excel’s way to show that you entered the formula with CTRL-SHIFT-ENTER). This provides a rudimentary type of iteration, where the formula will be executed for each element in a range. This feature is in Excel help but IMHO the documentation leaves something to be desired. There are lots of explanations and examples around the web. ---
**Author:** ![jjimm](https://avatars.discourse-cdn.com/v4/letter/j/ba8739/32.png) [@jjimm](https://boards.straightdope.com/u/jjimm)\ **Post date:** [October 14, 2011, 1:33pm UTC](https://boards.straightdope.com/t/excel-question-countif-by-month/599415/25 "2011-10-14T13:33:19Z")
> [@CookingWithGas](#): > > I have not tested this particular solution but I have written dozens of SUMPRODUCT formulas where it is required. =SUMPRODUCT(1\*(MONTH(A1:A100)=11)) works just fine. ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [October 14, 2011, 10:39pm UTC](https://boards.straightdope.com/t/excel-question-countif-by-month/599415/26 "2011-10-14T22:39:39Z")
> [@jjimm](#): > > =SUMPRODUCT(1\*(MONTH(A1:A100)=11)) works just fine. It’s the same principle, forces coercion to a numeric. You could do “0+” to. But in some cases, the coercion is required. ---
**Author:** ![445588](https://avatars.discourse-cdn.com/v4/letter/4/e99b99/32.png) [@445588](https://boards.straightdope.com/u/445588)\ **Post date:** [April 27, 2013, 4:19pm UTC](https://boards.straightdope.com/t/excel-question-countif-by-month/599415/27 "2013-04-27T16:19:28Z")
To the excellent minds of straight dope, > [@](#): > > Do you just need to count the number of rows that have November dates, or are you trying to count the number of items in B with November dates in A? > Code: > 01/11/11 5 > 02/11/11 1 > 05/10/11 3 > 03/09/11 2 > Should your count be 2 (for the first two rows) or 6 (for the sum of B in the first two rows)? I followed your answers with interest until I found out that you had answered the first of these propositions not the second. I would be quite interested to find out how to count the sum of B with November dates in A. Thanks for clarifying this for me. ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [April 27, 2013, 7:31pm UTC](https://boards.straightdope.com/t/excel-question-countif-by-month/599415/28 "2013-04-27T19:31:43Z")
> [@445588](#): > > I would be quite interested to find out how to count the sum of B with November dates in A. I haven’t taken the time to test this but I believe that would be =SUMPRODUCT(1\*(MONTH(A1:A100)=11),B1:B100) ---
**Author:** ![445588](https://avatars.discourse-cdn.com/v4/letter/4/e99b99/32.png) [@445588](https://boards.straightdope.com/u/445588)\ **Post date:** [April 27, 2013, 7:46pm UTC](https://boards.straightdope.com/t/excel-question-countif-by-month/599415/29 "2013-04-27T19:46:18Z")
Yes, that works. Thanks very much! ---
**Author:** ![GennieGeo](https://avatars.discourse-cdn.com/v4/letter/g/c89c15/32.png) [@GennieGeo](https://boards.straightdope.com/u/GennieGeo)\ **Post date:** [September 25, 2013, 11:00pm UTC](https://boards.straightdope.com/t/excel-question-countif-by-month/599415/30 "2013-09-25T23:00:57Z")
So…I am having a similar issue but my Database will have 150 rows 150 column with titles but data will be dates a lot of cells will not have anything in them so I need a formula that will exclude empty cells then on a separate work sheet a summary basically look at database if month and year match this cell(a) count, trouble Im having is it looks at DD/MM/YY I need it to look at everything in “Jan 2013” not just Jan 1 so a of range Jan1 - 31, 2013 Sum it up I need a formula to look at the data in the database and tell me how many tickets expire in each month/year based of supplied criteria in a summary page I know its doable just unsure if I’m approaching it the right way Thanks in advance for all your help ---
**Author:** ![Asympotically\_fat](https://avatars.discourse-cdn.com/v4/letter/a/e47c2d/32.png) [@Asympotically\_fat](https://boards.straightdope.com/u/Asympotically_fat)\ **Post date:** [September 25, 2013, 11:05pm UTC](https://boards.straightdope.com/t/excel-question-countif-by-month/599415/31 "2013-09-25T23:05:07Z")
… ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [September 26, 2013, 8:08pm UTC](https://boards.straightdope.com/t/excel-question-countif-by-month/599415/32 "2013-09-26T20:08:31Z")
I suggest you register for free at [this Excel site](http://www.excelforum.com/microsoft-office-application-help-excel-help-forum/) and post your question. You can attach a file there, which you can’t do here. ---
**Author:** ![sirpaul](https://avatars.discourse-cdn.com/v4/letter/s/ecc23a/32.png) [@sirpaul](https://boards.straightdope.com/u/sirpaul)\ **Post date:** [June 4, 2015, 9:36pm UTC](https://boards.straightdope.com/t/excel-question-countif-by-month/599415/33 "2015-06-04T21:36:20Z")
Thanks BubbaDog!! ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [June 5, 2015, 12:53am UTC](https://boards.straightdope.com/t/excel-question-countif-by-month/599415/34 "2015-06-05T00:53:33Z")
> [@sirpaul](#): > > Thanks BubbaDog!! Did you become a member today just to thank someone for a post from 4 years ago? ---
**Author:** ![BubbaDog](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/bubbadog/32/333_2.png) [@BubbaDog](https://boards.straightdope.com/u/BubbaDog)\ **Post date:** [June 5, 2015, 10:38pm UTC](https://boards.straightdope.com/t/excel-question-countif-by-month/599415/35 "2015-06-05T22:38:21Z")
> [@CookingWithGas](#): > > Did you become a member today just to thank someone for a post from 4 years ago? And I gotta say, I’m quite flattered. ---
**Author:** ![dianaze](https://avatars.discourse-cdn.com/v4/letter/d/82dd89/32.png) [@dianaze](https://boards.straightdope.com/u/dianaze)\ **Post date:** [February 4, 2016, 11:14pm UTC](https://boards.straightdope.com/t/excel-question-countif-by-month/599415/36 "2016-02-04T23:14:59Z")
Have a question about Finding the month in one column (when you enter any date within that month) and then looking to count if a specific text in in the 2nd column. So in counting the instances of a word such as Safety in column B, but checking for Nov in Column A. Then getting a sum for all Safety occurances in the month of November. If anyone could help, I would be grateful. Thanks ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [February 5, 2016, 11:28am UTC](https://boards.straightdope.com/t/excel-question-countif-by-month/599415/37 "2016-02-05T11:28:58Z")
> [@dianaze](#): > > Have a question about Finding the month in one column (when you enter any date within that month) and then looking to count if a specific text in in the 2nd column. > > So in counting the instances of a word such as Safety in column B, but checking for Nov in Column A. > Then getting a sum for all Safety occurances in the month of November. If anyone could help, I would be grateful. > > Thanks \*Exactly \*what format is the data in for the month in one column? Is it dates, or text? (Best practice is to use date data when dealing with dates.) Also see my post a few above that recommends a site (free registration required) where you can post your file along with your question. I am a moderator at that site. ---
**Author:** ![michany17](https://avatars.discourse-cdn.com/v4/letter/m/ba9def/32.png) [@michany17](https://boards.straightdope.com/u/michany17)\ **Post date:** [March 15, 2016, 3:50pm UTC](https://boards.straightdope.com/t/excel-question-countif-by-month/599415/38 "2016-03-15T15:50:40Z")
This information has been very helpful, but how do I get a count of all dates in a range of multiple columns and rows - Say B2:V50 - that equal November? ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [March 17, 2016, 3:12am UTC](https://boards.straightdope.com/t/excel-question-countif-by-month/599415/39 "2016-03-17T03:12:43Z")
> [@michany17](#): > > This information has been very helpful, but how do I get a count of all dates in a range of multiple columns and rows - Say B2:V50 - that equal November? How is the data stored–as dates? A date cannot equal November. Its \*month \*can be November–is that what you mean? Use this formula as an array formula. If you are not familiar with an array formula, Excel will iterate the expression over the given range. You must enter this into a cell by typing it and then hitting CTRL-SHIFT-ENTER, not just ENTER. =SUM(IF(MONTH(B2:V50)=11,1,0)) If you do it correctly it will look like this in the formula bar: {=SUM(IF(MONTH(B2:V50)=3,1,0))} But you can’t just type in the braces, you have to hit CTRL-SHIFT-ENTER. ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [March 17, 2016, 3:14am UTC](https://boards.straightdope.com/t/excel-question-countif-by-month/599415/40 "2016-03-17T03:14:30Z")
> [@michany17](#): > > This information has been very helpful, but how do I get a count of all dates in a range of multiple columns and rows - Say B2:V50 - that equal November? Looking back on this thread I see a number of solutions for the original problem, and I think they will all work if you put your B2:V50 range in there. Did you try _any_ of them? [Previous page](https://boards.straightdope.com/t/excel-question-countif-by-month/599415.md?page=1) [Next page](https://boards.straightdope.com/t/excel-question-countif-by-month/599415.md?page=3)