Count number of entries that have a date stamp of last week

[chive90]

In Sheet2 Column F I have a date in DD/MM/YYYY format.
In Sheet 2 Column G I have the source which is listed as either "A" or "B"

In Sheet1 I have a summary table.
In cell B10 of Sheet1 I would like a count of Sheet2 entries that have last week's date AND have a source of A.
In cell B11 of Sheet1 I would like a count of Sheet2 entries that have last week's date AND have a source of B.

So at the time of writing this (20th October) that would be any that fall between 9th-15th October. This range will change to 16th-22nd October on Monday 23rd October.

I also require a SUM of Sheet2 Column C based on the above criteria (date stamp and source A, date stamp and source B) which will be entered in Sheet1 C10 and C11 respectively
and a SUM of Sheet2 Column D based on the above criteria (date stamp and source A, date stamp and source B) which will be entered in Sheet1 D10 and D11 respectively

I imagine I can use a COUNTIFS for the first requirement and a SUMIFS for the second and third requirements but I am not sure how to add in the date element nor how this impacts the formulas.

Thanks for any help

 

[Joe4]

Have a look here for how to use a date range criteria for COUNTIFS and SUMIFS formulas:

How to COUNT values between two dates

A quick and easy way to count between dates is by using the COUNTIFS formula. Create formula-based filters, find your sales figures, or organize your tables in no time.

www.spreadsheetweb.com

Of course, with COUINTIFS and SUMIFS, you can add more than two conditions, so entering source criteria to that should be a trivial addition.

 

[chive90]

Have a look here for how to use a date range criteria for COUNTIFS and SUMIFS formulas:

How to COUNT values between two dates

A quick and easy way to count between dates is by using the COUNTIFS formula. Create formula-based filters, find your sales figures, or organize your tables in no time.

www.spreadsheetweb.com

Of course, with COUINTIFS and SUMIFS, you can add more than two conditions, so entering source criteria to that should be a trivial addition.

Thanks but the dates are constantly moving, they aren't static. It always needs to count the previous week, which, depending on when the file is opened, will be different.

 

[Joe4]

You don't have to hard code the dates - you can reference cells that hold the dates you want to use or use formulas to calculate the dates you want.

 

[Joe4]

If your real issue is how to calculcate the Monday of the previous week, see this thread here: Previous Monday Date
It looks like this formula will return that for you:

=TODAY()-WEEKDAY(TODAY())-5

Then, to get the Sunday ending that week, just add 6 to the above value.

 

[chive90]

If your real issue is how to calculcate the Monday of the previous week, see this thread here: Previous Monday Date
It looks like this formula will return that for you:

=TODAY()-WEEKDAY(TODAY())-5

Then, to get the Sunday ending that week, just add 6 to the above value.

Thanks. I also saw a thread which suggested using the following where TODAY() is in A1:
=A1-WEEKDAY(A1,3)-7 to get last Monday and;
=A1-WEEKDAY(A1,3)-1 to get last Sunday

Can you explain how these differ to your formula?

I managed to use the above in my COUNTIFS formula per your suggestion, by referencing both date cells using ">=" and "<=" and it appears to be working so thank you.

 

[Joe4]

Thanks. I also saw a thread which suggested using the following where TODAY() is in A1:
=A1-WEEKDAY(A1,3)-7 to get last Monday and;
=A1-WEEKDAY(A1,3)-1 to get last Sunday

Can you explain how these differ to your formula?

The formulas can look different by return the same value, due to the different values of the 2nd argument of the WEEKDAY function (see here: WEEKDAY function).
Based on which day of the week you pick for the start of the week, you would subtract a different number of days.