#### benste

##### New Member
Hi,

I am really new to excel and I am trying to do something that I cant get to work....

If I have the column that shows the dates:

A1 04/02/2012
A2 04/02/2012
A3 03/02/2012
A4 04/02/2013
A5 04/02/2013
A6 04/02/2013

How can I figure how how many columns represent events that happened in April 2012?

I thought it was with =COUNTIF((A1:A6,"*04*")+(A1:A6,"*2012*" - but its not working.

Thanks,

b

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Hi,

I think you want something like this:

=COUNTIFS(A:A,">04/01/2012",A:A,"<04/30/2012")

Last edited:
=countif(A1:A6,A1)=1

Another option just in case your version is < Excel 2007...

=SUMPRODUCT(--(MONTH(A\$1:A\$6)=4),--(YEAR(A\$1:A\$6)=2012))
or as entire column (although I don't like it)
=SUMPRODUCT(--(MONTH(A:A)=4),--(YEAR(A:A)=2012))

Last edited:
This worked great thank you!!!

Would I do the same thing if I was looking for in this example how many Interns where hired for HR?

A1 Intern
A2 Intern
A3 New Hire
A4 New Hire
A5 Intern
A6 New Hire

B1 Finance
B2 HR
B3 HR
B4 Finance
B5 HR
B6 Finance

And how would I use wildcards for this? So if someone wrote HR Dept. in a field instead of just HR?

Thanks again - this was amazing!

Ben

Welcome to the MrExcel board!

Glad you got a quick & successful outcome to your first question.
However, for the future, please note point 12 of the Posting Guidelines

First of all can you just acknowledge that you have read Peter's post #6.
Secondly it is now more important because of the wilcards that you let us know what version of Excel you are using.

Hi Mark,

Sorry, im new to this thing - i replied to Peter but I guess it was more like a personal reply to apologize.

I am using either Excel 2011 for Mac or Numbers for mac.

Also, I thought since it was a different question then I should start a new thread. I didn't realize that you should stay in the same thread. Any other helpful advice?

thanks

ben

FDibbens has given you an answer in the other thread (FDibbens didn't realise that you had already asked the question) and you could do something very similar like
=COUNTIFS(A1:A6,"Intern*",B1:B6,"*HR*")
but the answer FDibbens gave is more flexible, both only work with 2007 upwards which is why it is important that you quote the version you are using.
I assume that both work with Mac as I don't have one to test on.

Also, I thought since it was a different question then I should start a new thread. I didn't realize that you should stay in the same thread.
Normally you would start a new thread but once you have asked the question stick with it or it leads to people duplicating answers

Last edited:
i replied to Peter but I guess it was more like a personal reply to apologize.
Did you send a PM? I didn't receive one.
Mind you, a PM is not necessary you just need to know that your questions are not urgent to other people.

Also, I thought since it was a different question then I should start a new thread. I didn't realize that you should stay in the same thread. Any other helpful advice?
Whether to start a new thread or not can be a bit tricky. If the new question is really an extension of the old one, then best to stay in the old thread. If the question is different, but related to the old one, then you can start a new thread but it is a good idea to put a link to the old thread in your new one so people can see the history of what has been discussed/suggested. Having said that, the distinction can still be blurred and you just have to make the best assessment you can.

Advice is to look at all the Guidelines in my previous link as well as the Forum Rules and FAQs.
Small screen shots of your sheet can also be useful.

See my signature block for links for all those things.

Last edited:

Replies
2
Views
258
Replies
6
Views
222
Replies
29
Views
829
Replies
3
Views
619
Replies
4
Views
669

1,202,977
Messages
6,052,890
Members
444,608
Latest member
Krunal_Shah

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back