Urgent HELP: COUNTIF advice needed please...

[benste]

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

 

[Bairn90]

Hi,

I think you want something like this:

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

 

[Lplater]

=countif(A1:A6,A1)=1

 

[MARK858]

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

 

[benste]

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

 

[Peter_SSs]

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

 

[MARK858]

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.

 

[benste]

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

 

[MARK858]

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

 

[Peter_SSs]

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.