SUM data IF criteria AND within a bounded date range

[rodrigje]

Hi All,

I am trying to collect data within a week (Summary tab) period for another worksheet (List tab) that I am adding info to on a daily basis. I am trying to sum up for each salesperson (col. E); the total time (Col. J) spent on calls for a week ending date. I have the Summary sheet containing Date (col. A), Day (Col. C), and Weekending date (Col. B) in 3 separate columns but what I need is a cell formula that will give me the sum of time for a given salesperson (set up in a column A for List tab) within the boundary of a 7 day week from the latest Weekending Date available from the List Tab. I hope the details help, I am still very new to nesting thanks for any help provided.

If it is hairy- I can also work with just sum if salesperson- time and with weekending date criteria (without considering within 7 days).

 

[Aladin Akyurek]

Hi All,

I am trying to collect data within a week (Summary tab) period for another worksheet (List tab) that I am adding info to on a daily basis. I am trying to sum up for each salesperson (col. E); the total time (Col. J) spent on calls for a week ending date. I have the Summary sheet containing Date (col. A), Day (Col. C), and Weekending date (Col. B) in 3 separate columns but what I need is a cell formula that will give me the sum of time for a given salesperson (set up in a column A for List tab) within the boundary of a 7 day week from the latest Weekending Date available from the List Tab. I hope the details help, I am still very new to nesting thanks for any help provided.

If it is hairy- I can also work with just sum if salesperson- time and with weekending date criteria (without considering within 7 days).

=SUMPRODUCT(--(SalesPersonRange=SalesPerson),--(DateRange>=StartDate),--(DateRange<=EndDate),SumRange)

Alternatively: Construct a pivot table where dates are grouped by weeks.

 

[rodrigje]

That is too cool, do the ",--" allow me further sub-criteria? Just curious to know. Thanks a bunch!

 

[Aladin Akyurek]

That is too cool, do the ",--" allow me further sub-criteria? Just curious to know. Thanks a bunch!

Yes.

 

[rodrigje]

That's Great! Thank you.

Jesse