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
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
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
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
sumproduct count month.xlsx
