Count number of remaining dates this day / week / month / year

[sparkytech]

I have a sheet with dates listed in columns B:12 to C:15 in a table. Column "B" is "Start" and column "C" is "End". Column A:12 to A:15 has a drop down list with choices of "Not Started", "Started", "Completed", and "Cancelled". The sheet calculates how many dates remain for the day, week, month, and year. It sort of works. I want to calculate these values only if the value in "A" is "Not Started" or "Started". I am also having problems calculating the "Week" value, as I want it to be the current week, not the next 7 days. I cant figure out how to enter a picture of my sheet, or the sheet itself. Any ideas what I am doing wrong?

Formulas below:

Today formula:
=SUMPRODUCT((INT(Table134346[[Start]:[End]])=TODAY())*1)

Tomorrow formula:
=SUMPRODUCT((INT(Table134346[[Start]:[End]])=TODAY()+1)*1)

This Week formula:
=COUNTIFS(Table134346[[Start]:[End]],">="&TODAY(),Table134346[[Start]:[End]],"<="&TODAY()+7)

This Month formula:
=SUMPRODUCT((MONTH(Table134346[[Start]:[End]])=MONTH(TODAY()))*(YEAR(Table134346[[Start]:[End]])=YEAR(TODAY())))

This Year formula:
=SUMPRODUCT(1*(YEAR(Table134346[[Start]:[End]])=(YEAR(TODAY()))))

 

[Kaper]

for current week (assuming it is Monday-Sunday):
TODAY()-WEEKDAY(TODAY(),3)

=COUNTIFS(Table134346[[Start]:[End]],">="&TODAY()-WEEKDAY(TODAY(),3),Table134346[[Start]:[End]],"<="&TODAY()-WEEKDAY(TODAY(),3)+6)

(note Sunday is Monday +6 ! if there is +7 then it is next Monday)

As for only two options: Started/not started, add to each of formulas additional condition "Started" and add second copy od formula with "Not Started".

As I have no access to your table (and it's structure), let me show on example:
=COUNTIFS(B:B,">="&TODAY()-WEEKDAY(TODAY(),3),B:B,"<="&TODAY()-WEEKDAY(TODAY(),3)+6)
this would be count of all rows in column B with date in current week
and this:
=COUNTIFS(B:B,">="&TODAY()-WEEKDAY(TODAY(),3),B:B,"<="&TODAY()-WEEKDAY(TODAY(),3)+6,C:C,"Started")+=COUNTIFS(B:B,">="&TODAY()-WEEKDAY(TODAY(),3),B:B,"<="&TODAY()-WEEKDAY(TODAY(),3)+6,C:C,"Not Started")
counts all dates within current week but with column C being Started or Not Started

(one could note that *Started could also do in this particular case (as we do not have "Possibly Started" etc. options, but for clarity, I'd go for this longer notation

 

[sparkytech]

Thanks for the help! The first formula worked great, but the second formula to check for "Started" is not working. How can I upload a sample of my spreadsheet?

 

[Peter_SSs]

How can I upload a sample of my spreadsheet?

Welcome to the MrExcel board!

See Attachments for good ways to provide sample data that we can copy/paste to test with.