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.
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
Thank you so much Purfleet !
Another suggestion is to use a Pivot Table and group by Months/Years