CODEWITHSUNDEEP
excelexcel-formula
Sam
Sam

Reputation: 736

Finding data for a determined week

I have a table and I have a date in cell B4 which is something like 15/03/2021

I want to count values where the start date is the value on cell B4 but also count everything till the end of that week. So everything from Monday 15/03/2021 to Sunday 21/03/2021

What I have so far is something like

=COUNTIFS(MySheet!A:A,"Y",MySheet!C:C,">="&B4)

My trouble is how to tell it "also count everything until sunday that week without having to manually put the end date into a cell

Upvotes: 0

Views: 60

Answers (3)

Chronocidal
Chronocidal

Reputation: 7951

Your question is slightly ambiguous, so there are two possible answers

To count for exactly 7 days:

=COUNTIFS(MySheet!A:A,"Y", MySheet!C:C,">="&B4, MySheet!C:C,"<"&B4+7)

To count until the end of the next Sunday: (N.B. On Sundays, this will only count 1 day)

=COUNTIFS(MySheet!A:A,"Y", MySheet!C:C,">="&B4, MySheet!C:C,"<"&B4+8-Weekday(B4,2))

This works by saying "on or after (>=) the date given", and "before (<) 7 days later / the following Monday"

Upvotes: 1

JvdV
JvdV

Reputation: 75850

I assume the question behind your question is how to account for dates in B4 other than a monday and keep counting untill the end of that same week, therefor you can use:

=COUNTIFS(Mysheet!A:A,"Y",Mysheet!C:C,">="&B4,Mysheet!C:C,"<="&B4+7-WEEKDAY(B4,2))

This does set a Sunday as the "end" of the week as per your OP.

Upvotes: 2

norie
norie

Reputation: 9857

Add 6 to the value in B4 to get the second criteria.

=COUNTIFS(Mysheet!A:A,"Y",Mysheet!C:C,">="&B4, Mysheet!C:C,"<="&B4+6)

Upvotes: 2

Related Questions