COUNTIF For the Current Week

M

Mista_sav

Board Regular
Joined
Aug 18, 2019
Messages
69
Office Version
  1. 2016
Platform
  1. Windows
  2. Web
Not sure why im finding this so difficult but im wondering if anyone can help.

I have one sheet that from columns E through to P are all dates. Some are in the future and what i need is a formula that will count all dates in the current working week.

This is about as far as i got. I tried a few tips online but could not for te life of me get it to work

=COUNTIFS('Call List'!E11:P30

Thank you
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
Peter_SSs said:
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
Click to expand...
alright i hope i did it right hahah i am on a work pc so they dont always allow stuff but is this what you were after. Basically i need to count all the dates in the current week. (Not sure if all the code is correct below or if its meant to present that way haha)


Post Approval & Settlement Call Tracking - WIP NEW STYLE.xlsm
EFHIJKLMNOP
10Follow Up call DuePost Aproval Call5 days Post6 Weeks3 Months6 Months12 Months18 Months24 Months30 Months36 Months
11 Friday, 27 January 2023Friday, 27 January 2023Sunday, 5 March 2023Saturday, 22 April 2023Saturday, 22 July 2023Monday, 22 January 2024Monday, 22 July 2024Wednesday, 22 January 2025Tuesday, 22 July 2025Thursday, 22 January 2026
12 Saturday, 28 January 2023Saturday, 28 January 2023Monday, 6 March 2023Sunday, 23 April 2023Sunday, 23 July 2023Tuesday, 23 January 2024Tuesday, 23 July 2024Thursday, 23 January 2025Wednesday, 23 July 2025Friday, 23 January 2026
13 Sunday, 29 January 2023Sunday, 29 January 2023Tuesday, 7 March 2023Monday, 24 April 2023Monday, 24 July 2023Wednesday, 24 January 2024Wednesday, 24 July 2024Friday, 24 January 2025Thursday, 24 July 2025Saturday, 24 January 2026
14  Monday, 30 January 2023Wednesday, 8 March 2023Tuesday, 25 April 2023Tuesday, 25 July 2023Thursday, 25 January 2024Thursday, 25 July 2024Saturday, 25 January 2025Friday, 25 July 2025Sunday, 25 January 2026
15  Tuesday, 31 January 2023Thursday, 9 March 2023Wednesday, 26 April 2023Wednesday, 26 July 2023Friday, 26 January 2024Friday, 26 July 2024Sunday, 26 January 2025Saturday, 26 July 2025Monday, 26 January 2026
16  Wednesday, 1 February 2023Friday, 10 March 2023Thursday, 27 April 2023Thursday, 27 July 2023Saturday, 27 January 2024Saturday, 27 July 2024Monday, 27 January 2025Sunday, 27 July 2025Tuesday, 27 January 2026
Call List
Cell Formulas
RangeFormula
E11:E16E11=IF('Input Approval & Sett Date'!$F11="","",'Input Approval & Sett Date'!$F11)
F11:F16F11=IF($D11="","",$D11+1)
H11:H16H11=IF($G11="","",$G11+5)
I11:I16I11=IF($G11="","",$G11+7*6)
J11:J16J11=IF($G11="","",EDATE($G11,3))
K11:K16K11=IF($G11="","",EDATE($G11,6))
L11:L16L11=IF($G11="","",EDATE($G11,12))
M11:M16M11=IF($G11="","",EDATE($G11,18))
N11:N16N11=IF($G11="","",EDATE($G11,24))
O11:O16O11=IF($G11="","",EDATE($G11,30))
P11:P16P11=IF($G11="","",EDATE($G11,36))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A11:P3443Dates Occurringthis weektextNO
 
Upvote 0
Mista_sav said:
I have one sheet that from columns E through to P are all dates. Some are in the future and what i need is a formula that will count all dates in the current working week.
Click to expand...
Hi Mista,

I saw your post and thought it might be a good test for me to try and figure out.

If I understand you correctly, you want to count all of the dates within a range of cells only if they are dates within the Current Week. Using this week as an example, the Current Week would be 23-Jan to 27-Jan. If you need the full 7-days, or your first day is not MONDAY, then the formula would just need to be modified for the correct first and last day of the Current Week.

I first counted the days for each row and then summed them up.
The 2nd test used the entire range of days and placed the total into a single cell. The COUNTS were both 68 in my case.
I populated the Table with random dates between 23-JAN and 6-FEB.

Here's what I got...

Using the Table range to a single cell:
Excel Formula: 
=COUNTIFS(Table13[[1]:[12]],">="&(TODAY()-WEEKDAY(TODAY(),2)+1),Table13[[1]:[12]],"<="&(TODAY()-WEEKDAY(TODAY(),2)+7))

Using the Rows for each range:

Excel Formula: 
=COUNTIFS(Table13[@[1]:[12]],">="&(TODAY()-WEEKDAY(TODAY(),2)+1),Table13[@[1]:[12]],"<="&(TODAY()-WEEKDAY(TODAY(),2)+7))

1674806739811.png


Hopefully I got this right...
 
Upvote 0
zero269 said:
Hopefully I got this right...
Click to expand...
Thank you for your effort; you are way above my excel knowledge hahah but maybe i explained it wrong.

See my example above i conditionally formatted the dates so it would highlight the dates in the current week which shows a total of 4. (That part i could do pretty easy)

Now i know i can easily count the 4 manually, but what i was hoping for was a formula that i could write on another sheet that would count them; it would say something like Calls due this week: 4
 
Upvote 0
ctrl shift enter while entering this formula.
Excel Formula: 
=SUMPRODUCT(IFERROR(--(WEEKNUM(DATEVALUE(REPLACE('Call List'!E11:P30,1,SEARCH(",",'Call List'!E11:P30),"")))=WEEKNUM(TODAY()))*--(YEAR(DATEVALUE(REPLACE('Call List'!E11:P30,1,SEARCH(",",'Call List'!E11:P30),"")))=YEAR(TODAY())),0))
 
Upvote 0
How about
Excel Formula: 
=SUMPRODUCT(--(WEEKNUM(--E11:P30)=WEEKNUM(TODAY())))
 
Upvote 0
i get #VALUE error for this

Post Approval & Settlement Call Tracking - WIP NEW STYLE.xlsm
ABCEFHIJKLMNOP
1Post Approval/Settlement Call List
2
3All Calls Highlighted below are calls due to be made in the current Week. Calls due must be input in the Call Diary
4
5
6
7Calls Due#VALUE!
8
9
10Member #NameKO NumberFollow Up call DuePost Aproval Call5 days Post6 Weeks3 Months6 Months12 Months18 Months24 Months30 Months36 Months
1112345Test 1969461 Monday, 23 January 2023Friday, 10 February 2023Sunday, 19 March 2023Friday, 5 May 2023Saturday, 5 August 2023Monday, 5 February 2024Monday, 5 August 2024Wednesday, 5 February 2025Tuesday, 5 August 2025Thursday, 5 February 2026
121313Test 29494616541 Tuesday, 24 January 2023Saturday, 11 February 2023Monday, 20 March 2023Saturday, 6 May 2023Sunday, 6 August 2023Tuesday, 6 February 2024Tuesday, 6 August 2024Thursday, 6 February 2025Wednesday, 6 August 2025Friday, 6 February 2026
1331646Test 3949649646 Wednesday, 25 January 2023Sunday, 12 February 2023Tuesday, 21 March 2023Sunday, 7 May 2023Monday, 7 August 2023Wednesday, 7 February 2024Wednesday, 7 August 2024Friday, 7 February 2025Thursday, 7 August 2025Saturday, 7 February 2026
14541647984Test 49649649644 Thursday, 26 January 2023Monday, 13 February 2023Wednesday, 22 March 2023Monday, 8 May 2023Tuesday, 8 August 2023Thursday, 8 February 2024Thursday, 8 August 2024Saturday, 8 February 2025Friday, 8 August 2025Sunday, 8 February 2026
15616564Test 596496496Sunday, 5 February 2023Tuesday, 28 January 2020Saturday, 1 February 2020Monday, 9 March 2020Monday, 27 April 2020Monday, 27 July 2020Wednesday, 27 January 2021Tuesday, 27 July 2021Thursday, 27 January 2022Wednesday, 27 July 2022Friday, 27 January 2023
Call List
Cell Formulas
RangeFormula
H7H7=SUMPRODUCT(--(WEEKNUM(--E11:P30)=WEEKNUM(TODAY())))
A11:A15A11=IF('Input Data & Dates'!$A11="","",'Input Data & Dates'!$A11)
B11:B15B11=IF('Input Data & Dates'!$B11="","",'Input Data & Dates'!$B11)
C11:C15C11=IF('Input Data & Dates'!$C11="","",'Input Data & Dates'!$C11)
E11:E15E11=IF('Input Data & Dates'!$F11="","",'Input Data & Dates'!$F11)
F11:F15F11=IF($D11="","",$D11+1)
H11:H15H11=IF($G11="","",$G11+5)
I11:I15I11=IF($G11="","",$G11+7*6)
J11:J15J11=IF($G11="","",EDATE($G11,3))
K11:K15K11=IF($G11="","",EDATE($G11,6))
L11:L15L11=IF($G11="","",EDATE($G11,12))
M11:M15M11=IF($G11="","",EDATE($G11,18))
N11:N15N11=IF($G11="","",EDATE($G11,24))
O11:O15O11=IF($G11="","",EDATE($G11,30))
P11:P15P11=IF($G11="","",EDATE($G11,36))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A11:P3443Dates Occurringthis weektextNO
 
Upvote 0
count answer should be 5 sorry as P15 has a call due as well hidden away haha
 
Upvote 0
How about
Excel Formula: 
=SUMPRODUCT(--(WEEKNUM(IF(E11:P30="",0,E11:P30))=WEEKNUM(TODAY())))
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
simple reference changing weekly?
2
Replies
12
Views
434
kvsrinivasamurthy
K
E
  • Question
Forecasting with VBA or Power Pivot in Excel
Replies
0
Views
376
Emilio30
E
C
  • Question
COUNTIFS for Filtered Data, to Measure Date Ranges
Replies
2
Views
1K
Fluff
Fluff
S
  • Question
Formula for pulling the counts on items in i different locations but with same name
2
Replies
11
Views
144
svis89
S
O
  • Question
Count only one occurrence of all criteria being met
Replies
8
Views
440
KRice
K

Forum statistics

Threads
1,214,960
Messages
6,122,479
Members
449,088
Latest member
Melvetica

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top