COUNTIFS Function
Hello everyone,
需要一些帮助这个公式,思考ing i have too much going on here?
=COUNTIFS({Environmental Incident Tracking Range 1}, AND({Environmental Incident Tracking Range 3} = 1, YEAR({Environmental Incident Tracking Range 4} = 2020, {Environmental Incident Tracking Range 1}, "Leak")))
Environmental Incident Tracking is the sheet I am trying to pull from.
Basically, for every quarter, I am looking for the total number of leaks reported, for the year 2020
TIA!
Answers
-
Paul Newcome ✭✭✭✭✭✭
Try something like this instead...
=COUNTIFS({Environmental Incident Tracking Range 1}, "Leak", {Environmental Incident Tracking Range 3}, 1, {Environmental Incident Tracking Range 4}, IFERROR(YEAR(@cell), 0) = 2020)
If that does not work are you able to provide a screenshot of the source data with sensitive/confidential data removed, blocked, and/or replaced with "dummy/mock" data?
-
@Paul NewcomeCame up unparseable
I have attached 2 images from the other sheet, hope this helps! Appreciate it :)
-
Paul Newcome ✭✭✭✭✭✭
Can you copy/paste the formula that is throwing the error directly from the sheet to here?
-
=COUNTIFS({Environmental Incident Tracking Range 1}"Leak", {Environmental Incident Tracking Range 3} 1,{Environmental Incident Tracking Range 4} IFERROR(YEAR(@CELL),0) =2020
-
Paul Newcome ✭✭✭✭✭✭
There are commas missing from between the ranges and their criteria. You also need to make sure @cell is all lowercase as that little bit is case sensitive.
=COUNTIFS({Environmental Incident Tracking Range 1}, "Leak", {Environmental Incident Tracking Range 3}, 1, {Environmental Incident Tracking Range 4}, IFERROR(YEAR(@cell), 0) = 2020)
-
So it shows that the formula is working, but it is not populating anything
Does it matter that the field for Leaks or Additions is a drop down?
-
Paul Newcome ✭✭✭✭✭✭
Is it multi-select, and is the text exactly "Leaks", or are the other words included?
-
Single- select
My apologies, the drop-down option for leak is "Leak / Fuite", which i have now adjusted but still not working
-
Paul Newcome ✭✭✭✭✭✭
Ok. Try this...
=COUNTIFS({Environmental Incident Tracking Range 1},CONTAINS("Leak", @cell), {Environmental Incident Tracking Range 3}, 1, {Environmental Incident Tracking Range 4}, IFERROR(YEAR(@cell), 0) = 2020)
-
Unfortunately still no.
I have tried a few others myself and cant seem to figure it out. Which line in the formula pulls the Quarter?
-
Paul Newcome ✭✭✭✭✭✭
That would be something you would have to tell me. Which range contains the quarter?
-
It is range 3, which i have included in the formula
-
Paul Newcome ✭✭✭✭✭✭
Ok. And exactly what is in that range on the source sheet?
-
it is fed from another sheet, the options are 1, 2, 3 or 4 (4 quarters in the year)
-
Paul Newcome ✭✭✭✭✭✭
Ok. Going all the way back to the origin of the quarter, can you provide all formulas/cell links that get it onto the source sheet that we are referencing?
Help Article Resources
Categories
Check out theFormula Handbook template!
Try this:<\/p>
=IF(ISDATE([Event Date]@row), IF(AND([Event Date]@row > TODAY(), [Event Date]@row <= TODAY(30)), \"Less than 30 days from today\", \"More than 30 days from today\"), \"//www.santa-greenland.com/community/discussion/70853/\")<\/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":108267,"type":"question","name":"Combining IF Formula for Blank\/ Not Blank Cells","excerpt":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected Date\" null -Pending: Based on \"Anticipated Collcted Date\" not null and \"Collected Date\" null Below is what I have, but it's unparseable:…","snippet":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected…","categoryID":322,"dateInserted":"2023-07-28T17:23:40+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164288,"lastUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":32,"score":null,"hot":3381135147,"url":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","format":"Rich","lastPost":{"discussionID":108267,"commentID":387885,"name":"Re: Combining IF Formula for Blank\/ Not Blank Cells","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387885#Comment_387885","dateInserted":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+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-07-28T18:30:11+00:00","dateAnswered":"2023-07-28T18:22:11+00:00","acceptedAnswers":[{"commentID":387882,"body":"