Excel question - COUNTIF by month?

Factual Questions
21

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.

22

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.

23

Except it doesn’t require it - it uses 1 and 0. Or is that what the CTRL-ENTER is for?

24

I posted an explanation based on an earlier post and see that it has already been explained.

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.

25

=SUMPRODUCT(1*(MONTH(A1:A100)=11)) works just fine.

26

It’s the same principle, forces coercion to a numeric. You could do “0+” to. But in some cases, the coercion is required.

27

To the excellent minds of straight dope,

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.

28

I haven’t taken the time to test this but I believe that would be

=SUMPRODUCT(1*(MONTH(A1:A100)=11),B1:B100)

29

Yes, that works.
Thanks very much!

30

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

31

…

32

I suggest you register for free at this Excel site and post your question. You can attach a file there, which you can’t do here.

33

Thanks BubbaDog!!

34

Did you become a member today just to thank someone for a post from 4 years ago?

35

And I gotta say, I’m quite flattered.

36

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

37

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

38

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?

39

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.

40

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?