Forum

Notifications
Clear all

Counting Person(s) by Specific Dates/Months

4 Posts
3 Users
0 Reactions
63 Views
(@nearph)
Posts: 3
Active Member
Topic starter
 

Hi Phil/everyone,

 

I would like to ask what are the specific excel formula since I am having hard time creating it, I could say the if and else statement but can't properly able to work it out in the excel sheet.

 

So here's the scenario:

I want to know the number of '#' 'Name' in a specific date like for example Jan 1/1/2020 - 1/30/2020. 

 

Based on my spreadsheet, I want to know the January total from a specific person/name and feb and so forth.

 

more a programming approach:

if 'Ralph' (1/1/2020 - 1/30/2020) display January total 

 

I hope I was able to explain it properly here.

 
Posted : 19/03/2020 6:26 am
(@purfleet)
Posts: 412
Reputable Member
 

I think you want the EOMONTH function. Mynda wrote a blog on it a while ago https://www.myonlinetraininghub.com/excel-eomonth-function

Bascially EOMONTH give you the end of the month, so the last day of Jan would be EOMONTH(DATE(2020,1,1),0) and to get the first day you would need to do EOMONTH(DATE(2020,1,1),-1)+1 (the -1 takes us back to the 31st of December and the +1 makes it the 1st of Jan.

You then need to add in the greater than and less thans with a countif to get =COUNTIFS($A:$A,">="&EOMONTH(DATE(2020,1,1),-1)+1,$A:$A,"<="&EOMONTH(DATE(2020,1,1),0),$B:$B,$H8)

I dont like putting the date in this formula so i would put the 1/1/2020 in a cell and reference it as per the top example in the attched workbook.

PS i had to mess around with you dates to get them to work on a UK machine

Purfleet

 
Posted : 19/03/2020 12:48 pm
(@nearph)
Posts: 3
Active Member
Topic starter
 

Thank you so much Purfleet ! Smile

 
Posted : 20/03/2020 2:01 am
(@sunnykow)
Posts: 1417
Noble Member
 

Another suggestion is to use a Pivot Table and group by Months/Years

 
Posted : 20/03/2020 2:04 am
Share: