Hello clever Excel types.
Is it possible to use the COUNTIF formula to count the number of items in Col B, where Col A contains a date in November?
That is, the dates in Col A are stored in the format dd/mm/yy but I want all November dates (01/11/11, 02/11/11, 03/11/11, etc) to be counted.
=countif(month(a1:a100),11)
That formula returns an error message when I try it.
I don’t think you can use CountIf for that. CountIf counts over a range (e.g., A1:A5) and tests each cell in that range for a value.
To check each date for the Month, you’d need to use the Month function.
So your expression would be something like =COUNTIF(A1:A4,MONTH(A1:A4)=11).
But I don’t think that’s going to work because I don’t think the Month function will work right with a range. I think you’re going to have to use a column of IF statements and then sum or count them.
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?
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)?
Try this
=SUMPRODUCT(–(A$1:A$1000<>""),–(MONTH(A$4:A$1000)=11))
Where A1:A1000 is the list of dates
I know, it’s not Countif but I’m assuming that you want the result more than the process.
I love Excel conundra.
BubbaDog, great solution. What are the “–“s for though? ETA: you get the same result with =SUMPRODUCT((A$1:A$100<>””)*(MONTH(A$1:A$100)=11)) [presume you meant A$1:A$100 in the second sumproduct criterion]
(Alternatively you can just use a pivot table. Pivot tables are the answer to 99% of Excel dilemmas.)
The – is just a list notation for sum product. The function SUMPRODUCT by definition is already taking a list of criteria and doing a boolean multiplication so you can either explicitly write the formula or just supply the list of criteria.
The A$1:A$1000<>"" isn’t really necessary for this case (month =11). It’s in there in case the user wants to test for month = 1. Any blank field in the list would count as a 1 (January) if this list element was missing.
Wow, that’s pretty cool, BubbaDog.
It only works if you write it the way you have it though. If you try to use the function wizard it doesn’t come out right.
Confession: I actually suck at SUMPRODUCT and Array formulas and usually have to do some search to help me set it up. It’s a powerful function though and I try to use it on occasion.
If fact, while answering the OP’s request I had a DOH moment when I realized that a complex IF statement solution I put into a spreadsheet yesterday could have easily been reduced using SUMPRODUCT in the formula.
We’re all still learning.
Try this:
=SUM(–(MONTH(A1:A88) = 11))
When you enter it, instead of just hitting ENTER, hold down CTRL-SHIFT, then press ENTER. That makes it an array formula that BubbaDog mentions. It will show up in Excel like this:
{=SUM(–(MONTH(A1:A88) = 11))}
The curly braces mean it’s an array formula. Basically, it goes through each cell in the range, perfoms the MONTH calculation and the = comparison and returns true or false. The – converts the true/false into a 1 or 0, and the Sum function adds it up.
Or this:
=SUMPRODUCT(1*(MONTH(A1:A88)=11))
SumProduct is a secret array formula. The 1* does the same thing that – does; it turns the equality from a Boolean to an integer. That formula can just be entered the normal way (no need to hold down ctrl-shift).
Normally I’d say yes, but dates always seem to get screwed up more than any other field in a pivot table. If there’s even one non-date (or non-number) cell in the field the pivot table won’t recognize it as a date, and therefore won’t let you group the field by month. So by all means use a pivot table, but make sure you don’t have any non-dates first.
Plus, pivot tables are the spawn of the devil. They seem like they can do it all, but I’m always running across views they can’t seem to get right. And, they make every spreadsheet they’re in about 100 petabytes.
I have NO IDEA why that SUMPRODUCT thing works, but it works and so I am very happy! Thank you!!!
Setting them up can be an awful chore but Pivot Tables have been a blessing for me when I had to clean, format, or catagorize data.
sandra_nz, I’m glad to hear that it helped with your problem. I learned a few more things about SUMPRODUCT myself from the comments here. I had mistaken the notation about “–” and didn’t know that it was actually a Boolean indicator.
In plain English, for the versions BubbaDog and I posted, it’s going:
Question 1:
If there is anything at all in A1, assign the value “1” as the answer to this question. If there isn’t anything there, assign the value “0” as the answer to this question.
Question 2:
If the month in A1 is 11, assign the value “1” to this question. If the month isn’t 11, assign the value “0” to this question.
Now, multiply the answers from Question 1 and Question 2 together.
Now do this for A2 and so on, all the way down to the last cell mentioned.
THEN add the results from each row together.
So for any given row, if the answer to either question is “0”, then the result of the multiplication will be zero. You’ll only get a “1” if both questions are answered positively. So the final result will be only those cells where there is something there AND the month is 11. Then they all get added up and bada bing, all Novembers are counted. (A pivot table will do the same thing much more quickly and easily but as has been pointed out, it has several drawbacks.)
In later versions of the formula in this thread they’re able to dispense with the first question altogether by assuming that there will definitely be something in each cell to look at.
I have to say, the latest versions of Excel (2007 onwards) have really made working with pivot tables a hell of a lot easier.
Quite. And the file bloat can largely be solved by collapsing all the fields before you save the file…
Thanks jjimm, that explanatory kinda makes sense to me. 
You’ve all got me curious about pivot tables now. I used to use them in a previous job but haven’t really investigated them with Excel 2010.
I always found the biggest pain with them was the need to ‘refresh’ to see the latest data. I could remember it, but other users never could and they’d be constantly coming to me saying the spreadsheet was broken…