CODEWITHSUNDEEP
excelexcel-formula
Lewis Walker
Lewis Walker

Reputation: 13

COUNTIF formula using variable dates

Morning all, Let me explain my predicament! I'm putting together a set of reporting figures for my employer based on a spreadsheet containing work that's been completed so far this year. I need to report on the total volume of work completed and the average turnaround for this work, split by Week to Date, Month to Date, and Year to Date (WTD, MTD, YTD). This is something i've put together manually so far but i want to produce a formula which allows my employer to generate his own figures based on a week-ending date he can pick from a dropdown list (held in cell D4).

So far i've been able to generate the below COUNTIF formula to give me the WTD figures for work volumes completed within 7 days of the week ending date in cell D4 (with the completion date being held in 'Completed!O:O'):

=COUNTIFS(Completed!$O:$O,">="&D4-6,Completed!$O:$O,"<="&D4+1)

I'm also using the below AVERAGEIF formula to show the average turnaround (the Turnaround figure is held in 'Completed!P:P'):

=AVERAGEIFS(Completed!$P:$P,Completed!$O:$O,">="&D4-6,Completed!$O:$O,"<="&D4+1)

I've been trying to come up with similar formulas to tell me the MTD and YTD figures but with no success. I originally had the MTD formula return the value based on work completed within 31 days of the Week-Ending date but this was incorrect. If the week-ending date of 03/02/2017 if selected then i only need it to show the count of work completed between the 1st and 3rd of Feb. I'm sure once i work this one out that i'll be able to use a similar formula to show me YTD figures.

Any help or guidance you can offer is appreciated! Cheers.

Upvotes: 1

Views: 834

Answers (2)

Lewis Walker
Lewis Walker

Reputation: 13

a fellow helpful user has assisted and i now have formulas that work.

For MTD, i'm using the following:

=COUNTIFS(Completed!$O:$O,">="&EOMONTH(D4,-1)+1,Completed!$O:$O,"<="&D4+1)

And for YTD:

=COUNTIFS(Completed!$O:$O,">="&DATE(YEAR(D4),1,1),Completed!$O:$O,"<="&D4+1)

Thanks for everyones help!

Upvotes: 0

Chindraba
Chindraba

Reputation: 870

For the MTD count, use:

=COUNTIFS(Completed!$O:$O,">="&DATE(YEAR(D4),MONTH(D4),1),Completed!$O:$O,"<="&D4+1)

For the YTD count, use:

=COUNTIFS(Completed!$O:$O,">="&DATE(YEAR(D4),1,1),Completed!$O:$O,"<="&D4+1)

For the MTD average, use:

=AVERAGEIFS(Completed!$P:$P,Completed!$O:$O,">="&DATE(YEAR(D4),MONTH(D4),1),Completed!$O:$O,"<="&D4+1)

For the YTD average, use:

=AVERAGEIFS(Completed!$P:$P,Completed!$O:$O,">="&DATE(YEAR(D4),1,1),Completed!$O:$O,"<="&D4+1)

Upvotes: 1

Related Questions