CountIf with Date range
I am trying to count the number of requests types between a date range. This is what I thought would work:
=COUNTIF(({Request Tracker Range 4 - Request Type}, Category5), AND({Request Tracker Range 3 - Date Requested}, DATE(2019, 01, 02:2019, 03, 14)))
I am using this formula to count the entire column =COUNTIF(({Request Tracker Range 4 - Request Type}, Category5) and it works fine. When I throw the date range into the mix, I am no longer getting results.
Any suggestions would be appreciated!
Best Answer
-
Paul Newcome ✭✭✭✭✭✭
@Kendra PYou would need to use an AND function. I also suggest referencing a month and year number as opposed to two separate dates (in my opinion it is easier to maintain/update).
=COUNTIFS([Date Due]:[Date Due], AND(IFERROR(MONTH(@cell), 0) = 8, IFERROR(YEAR(@cell), 0) = 2022))
Answers
-
Paul Newcome ✭✭✭✭✭✭
A few issues with your above formula.
1. When using more than one set of criteria, you need to use a COUNTIFS instead.
2. You are closing the COUNTIF function before the additional criteria which means it is not being included in the calculations and is also not the correct syntax (thus the error).
3. When using the AND function within a COUNTIF(S) or SUMIF(S), you establish the range first and then use the AND function for the criteria along with@cellreferences.
4. When using multiple dates, you need to use multiple DATE functions.
Give something like this a shot...
=COUNTIFS({Request Tracker Range 4 - Request Type}, Category5, {Request Tracker Range 3 - Date Requested}, AND(@cell>= DATE(2019, 01, 02),@cell<= DATE(2019, 03, 14)))
-
lnelsen ✭✭
Paul, thank you for feedback and tips!
-
Paul Newcome ✭✭✭✭✭✭
Happy to help!
Let me know if you need any help getting something working the right way for you.
-
I'm having a similar problem.
I am creating a YTD summary of report deliveries (we are a test laboratory and our deliverables are reports)
Range 2 is a company location "EMP"
Range 3 is a date field of when a report was formally released to a customer
Goal is to count the number of reports for each month by Company location (we have 2 locations EMP and MLB) (The formula is me trying to get a January summary of number of reports for each location working).
I get a #UNPARCABLE error.
Here is my formula.
=COUNTIFS({EMP MLB Report Tracking Range 2}, EMP, {EMP MLB Report Tracking Range 3}, AND(@cell >= DATE(2020, 01, 01), @cell <= DATE(2020, 01, 31)))
Here is the table its pulling from (location is
Your assistance is greatly appreciated!!
Thank you
Jenelle Gullickson - Element Materials Technology
-
Paul Newcome ✭✭✭✭✭✭
You are missing quotes from around "EMP".
=COUNTIFS({EMP MLB Report Tracking Range 2},"EMP", {EMP MLB Report Tracking Range 3}, AND(@cell >= DATE(2020, 01, 01), @cell <= DATE(2020, 01, 31)))
-
Hi -
I am trying to following your wonderful instructions. I am simply just trying to count how many times I have contacted a rep "this week". I add dates in a column when I reached out. I want to then add this Metric in a Dashboard. Is there a way to have this dynamic to where it will automatically show numbers for the current week or do I manually enter the dates in?
The Column = Last Date Rep Contacted (I also will duplicate the formula for how many times I contacted a Customer, etc.)
公式:=条件统计([最后日期代表联系]1:[拉斯维加斯t Date Rep Contacted]1021, AND(@cell >= DATE(2020, 2, 17), @cell <= DATE(2020, 2, 23)))
Thank you for your time.
-
Paul Newcome ✭✭✭✭✭✭
You could use a WEEKNUMBER and YEAR function to compare to the current week number and year. This does use the TODAY() function, so you will need to activate the sheet regularly to have the metrics updated. The WEEKNUMBER function also pulls Monday - Sunday instead of Sunday - Saturday.
The formula itself would look something like this...
=COUNTIFS([Last Date Rep Contacted]1:[Last Date Rep Contacted]1021, AND(IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))
-
Sorry I am not educated on this but do I need fill in the Weeknumber, Year or Today dates?
-
Paul Newcome ✭✭✭✭✭✭
As long as the range is correct (column name(s) and row numbers), you should be able to drop in exactly what I have posted.
-
Thank you! I had the cell only fixed for dates and I pasted it in a regular cell and it pulled up just fine! Is there a way to put this so I can reference the number but not on this sheet?
I basically want to use the Metric widget to add to a dashboard on how many accounts we contacted, etc. Right now I have to add this at the bottom of the sheet and pull the number from that Master sheet right?
Thank you so much for your time and help.
-
Paul Newcome ✭✭✭✭✭✭
There are many different options for pulling this to reflect in a Metric Widget. One option would be to create a Sheet Summary field, drop the formula in there, then reference that field in your widget.
To pull this data onto another sheet, you would start typing out the formula until you get to here...
=COUNTIFS(
Then just below where you are typing, you should see a helper box that has a link in it that says "Reference another sheet". You would click on this link, select the appropriate sheet, select the desired range (click on the column header to select the entire column if needed), then click the button in the bottom right corner that says "Insert Reference".
Now it should look something like this...
=COUNTIFS({Other Sheet Name Range 1}
Finish typing out the rest of the formula, and the end result would look something like this:
=COUNTIFS({Other Sheet Name Range 1}, AND(IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))
-
Hi Paul, You have mentioned WEEKNUMBER pulls data from Monday-Sunday. Is there a way or other formula to make WEEKNUMBER pulls data from Saturday to Friday, that our usual cut-off.
Thank you in advanced.
-
Paul Newcome ✭✭✭✭✭✭
@Julius Benn SabenianoHow do you need to use this? What type of metrics are you trying to pull?
-
Hi Paul, I have a weekly report that needs to count all the leads per lead source and my source sheet is pulling the data from google sheets. To count the report automatically from the sheet source, I am using this formula. Select week column dictates the week number that I need. As you have mentioned, WEEKNUMBER pulls Monday to Friday, but this morning my manager asked to align this formula to our cut-off which is Saturday to Friday to tally on our reporting.
=COUNTIFS({CD1}, IFERROR(WEEKNUMBER(@cell), 0) = [Select Week]$1, {LS1}, [Lead Source]$5)
Is there a way to make the pulling of WEEKNUMBER function to Saturday to Friday?Any formula or helping columns needed? by the way source sheet is untouchable.
-
Paul Newcome ✭✭✭✭✭✭
I assume that {CD1} is a date type column?
Exactly what is in the [Select Week]$1 cell?
Help Article Resources
Categories
Check out theFormula Handbook template!
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-26T01:04:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-26T01:04:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"
Try this:<\/p>
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"