Idenfiying Months in Countifs formula

Resolved · High Priority · Version 2016

Fri 4 Jan 2019, 12:46 Delegate Chris said...

Chris has attended:
Excel Intermediate course

Idenfiying Months in Countifs formula

Hi, ive got a worksheet with a list of new starters and their start dates. Im trying to use the countif(s) function to give me a count of how many new starters begin in the month I'm currently in.

I.e If I open my worksheet on the 1st Feb it will know that we are in Feb and then count how many new starters have a start date in February.

And then if I open it in March the same formula will update automatically and give me a count of how many new starters have a start date in March.

Any help would be much appreciated ,many thanks

Fri 4 Jan 2019, 15:22 Trainer Jens said...

RE: Idenfiying Months in Countifs formula

Hi Chris,

Thank you for the forum question.

The Countifs cannot do what you would like to do, but the Sumproduct function can.

See example below:

=SUMPRODUCT(--(MONTH(A2:A35)=MONTH(TODAY()))*1)

In my example I have the start dates in the range from A2:A35. I am testing if the month number is the same as current month MONTH(A2:A35)=MONTH(TODAY()). This test will return true or false, but by adding two times minus in front of the test Excel returns 0 for a false test and 1 for a true test. By multiplying it with one I count how many true tests I have.

I hope this makes sense.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Fri 4 Jan 2019, 15:50 Delegate Chris said...

RE: Identifiying Months in Countifs formula

Hello Jens
Thank you for your swift reply, your example is in line with what id like to do but when I tried with my workbook it just returns a count of the cells within my range. I.e mine was H4:H35 so the count was 29. I have various dates populated in this range from 04/01/2019 to 10/06/2019 (in that format).I have 3 dates in Jan and four dates in Feb so ideally the count would be for 3 if I do it today. Again any guidance would be very gratefully appreciated

Fri 4 Jan 2019, 17:32 Trainer Jens said...

RE: Identifiying Months in Countifs formula

Hi Chris,

I cannot understand why it is not working.

Please have a look at my example (see attached file).

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector