CODEWITHSUNDEEP
exceldateexcel-formulaexcel-2013countif
FBeveridge
FBeveridge

Reputation: 121

COUNTIF for date is within the current week and for in the last week

I am creating a refreshable spreadsheet which pulls activities from SQL queries.

On the first page I need a formula that counts all the occurrences of a contact method that falls in the current week and the count of all occurrences that fall in the previous week.

Here is what my data set looks like:

Contact Employee  Date              Method  
Jack    John      12/16/15          Email
Jack    John      12/7/15           Email
Jill    John      12/9/15           Call
Rick    Amber     12/8/15           Call
Dave    Sarah     12/10/15          Email
Dave    Sarah     12/15/15          Call
Dave    Sarah     12/9/15           Email
Don     Amber     12/14/15          Call

What I want is for the data to return like this:

TimeFrame      #ofCalls  #ofEmails
Current Week      2          1
Last Week         2          3

I want to use a formula that looks for dates that fall with the current week and counts the occurrence and then another formula that looks for dates that fall within the prior week and counts the occurrences in that week.

I'd like the formula to stay the same so if I refresh the sheet each day, I don't have to change the date range in the formula.

Upvotes: 1

Views: 24864

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

There are at least two methods.

Using formulas, and assuming the first day of the week is a Monday.

Current Week Start Date:  =TODAY()+1-WEEKDAY(TODAY()-1)
Last Week Start Date:     =CurrentWeekStart -7

Current Week Email (using a table and structured references)

=COUNTIFS(Table1[Date],">="&CurrentWeekStart,Table1[Date],"<"&CurrentWeekStart+7,Table1[Method],"Email")

Last Week Emails

=COUNTIFS(Table1[Date],">="&LastWeekStart,Table1[Date],"<" & CurrentWeekStart,Table1[Method],"Email")

For the Calls, just change "Email" to "Calls" in the above formulas.

A second method would be to use a Pivot Table

Drag Dates to Rows; Method to Columns; Method to Values; Group Dates by 7 Days and ensure first date is on the start date of a week; Format to taste.

Use filters if you only want to see two weeks.

enter image description here

Upvotes: 3

Related Questions