Amount of Requests per Lawyer
Hello,
I'm trying to find out how many requests a lawyer gets per month based on a few things:
-AFID (Auto Fill-In Date)
-Type of Request = FOIA
-Lawyer's Staff name
-Status (Red, Yellow, Green, Blue;However, these are not in as words, they're in as "Visual Symbols")
This is what I have right now (changing name for anonymity purposes): =COUNTIFS({AFID}, AND(IFERROR(MONTH(@cell), 0) = 12, IFERROR(YEAR(@cell), 0) = 2019, {Type of Request}, FIND("FOIA", @cell), AND(IF({Law Staff}, FIND("Karen Cappa", @cell), IF({Status} = GREEN) > 0)
Answers
-
Paul Newcome ✭✭✭✭✭✭
Try this one...
=COUNTIFS({AFID}, AND(IFERROR(MONTH(@cell), 0) = 12, IFERROR(YEAR(@cell), 0) = 2019), {Type of Request}, FIND("FOIA", @cell) > 0, {Law Staff}, FIND("Karen Cappa", @cell) > 0, {Status}, "Green")
-
fennerb ✭✭✭
It came back as "INCORRECT ARGUMENT SET." :/
-
Paul Newcome ✭✭✭✭✭✭
Can you copy the formula that is generating the error from the sheet itself and paste it here so I can see EXACTLY what you are using?
-
fennerb ✭✭✭
=COUNTIFS({AFID}, AND(IFERROR(MONTH(@cell), 0) = 12, IFERROR(YEAR(@cell), 0) = 2019), {Type of Request}, FIND("FOIA", @cell) > 0, {Law Staff}, FIND("Ivars Steins", @cell) > 0, {Status}, "Green")
-
Paul Newcome ✭✭✭✭✭✭
The formula itself should not be producing an error.
Check your ranges to see if there are any errors in them. That could also generate an error.
-
fennerb ✭✭✭
I refreshed the page, and it no longer produced an error. It did, however, provide 0 as a value.
-
Paul Newcome ✭✭✭✭✭✭
Keep in mind...
The FIND function is case sensitive. For example: If you have FOIA in your formula, but you have Foia in your sheet then it will not be counted.
-
Paul Newcome ✭✭✭✭✭✭
Also check your column types as well. Primarily the AFID column.
Make sure that it is a date type column. If it is an auto-generated type of column that provides a date/time stamp, then you will need to incorporate the DATEONLY function like so...
=COUNTIFS({AFID}, AND(IFERROR(MONTH(DATEONLY(@cell)), 0) = 12, IFERROR(YEAR(DATEONLY(@cell)), 0) = 2019), {Type of Request}, FIND("FOIA", @cell) > 0, {Law Staff}, FIND("Ivars Steins", @cell) > 0, {Status}, "Green")
-
fennerb ✭✭✭
The AFID column is auto-generated and provides a date/time stamp. So, that makes so much more sense to me now! "Type of Request" and "Law Staff" are single-select drop-down columns to make sure we have continuity and prevent any misspellings. The "Status" column is generated by a formula using the AFID date and when it was sent to the Law Department.
I just tried the new formula, and it still yielded 0 after I refreshed it a few times. I'm copying and pasting what I used for reference: =COUNTIFS({AFID}, AND(IFERROR(MONTH(DATEONLY(@cell)), 0) = 12, IFERROR(YEAR(DATEONLY(@cell)), 0) = 2019), {Type of Request}, FIND("FOIA", @cell) > 0, {Law Staff}, FIND("Ivars Steins", @cell) > 0, {Status}, "Green")
And, if we need it, here's the "Status" formula that we've been using to determine the colors(this was prior to me renaming the column "AFID")要求:如果(ISBLANK([信息自动填满in Date]1), "", IF(ISDATE([Date Entire Request is Complete/Sent to Law]1), "Blue", IF(TODAY() >= [Information Requested -Auto Filled in Date]1 + 14, "Red", IF(TODAY() >= [Information Requested -Auto Filled in Date]1 + 7, "Yellow", IF(TODAY() >= [Information Requested -Auto Filled in Date]1, "Green")))))
-
Paul Newcome ✭✭✭✭✭✭
Hmm... That is odd. Here are a couple more things to look into...
- This happens to me on occasion. When I am creating a cross sheet reference, I select the column header too quickly before the sheet has time to fully load. Double check that your ranges are in fact correct. If you selected the column before the sheet was completely loaded, the range could have reverted back to the cell in the top left corner of the sheet.
- Go to the source sheet and create a filter that matches exactly what you are trying to build a formula for. Enter the same ranges/criteria in the same order. See what happens when you apply that filter.
Let me know what the results are. We are fast approaching reaching out to support.
I had an issue a few weeks ago where I was getting obviously incorrect counts from a COUNTIFS. Support was able to refresh the sheet itself on the back-end which got it working correctly. I like to try to exhaust all other possibilities though before reaching out to them.
-
Eric M Oliveira Employee
Hello,
Happy to help, it seems in all of the above examples the syntax is slightly off with the COUNTIFS function. If you're trying to find out how many requests a lawyer gets per month based on a few things:
-AFID (Auto Fill-In Date)
-Type of Request = FOIA
-Lawyer's Staff name
-Status (Red, Yellow, Green, Blue;However, these are not in as words, they're in as "Visual Symbols")
you can achieve this with a COUNTIF function similar to the below.
=条件统计({AFID}、月(@cell) = 12, {AFID}, (@cell) = 2019, {Type of Request}, "FOIA", {Law Staff}, "Karen Cappa", {Status}, "Green")
Note: You'll want to make sure all of the cross-sheet references are referencing the correct columns. Also, that the text values are spelled exactly as they are on the reference sheet, Smartsheet is case sensitive when it comes to formulas.
如果你使用多选列你佤邦nt to utilize a formula similar to the below:
=条件统计({AFID}、月(@cell) = 12, {AFID}, (@cell) = 2019, {Type of Request}, HAS(@cell, "FOIA"), {Law Staff}, HAS(@cell, "Karen Cappa"), {Status}, "Green")
The topics we discussed are further outlined by the Help Center articles below.
If the issue continues after performing the above please clickHEREand we can set aside some time for a screen share to correct this issue.
Have a wonderful day. Thank you for contacting Smartsheet Support.
Cheers,
Eric
Smartsheet Technical Support
-
Paul Newcome ✭✭✭✭✭✭
I must be missing it... Here is a copy/paste of one of the above COUNTIFS:
=COUNTIFS({AFID}, AND(IFERROR(MONTH(DATEONLY(@cell)), 0) = 12, IFERROR(YEAR(DATEONLY(@cell)), 0) = 2019), {Type of Request}, FIND("FOIA", @cell) > 0, {Law Staff}, FIND("Ivars Steins", @cell) > 0, {Status}, "Green")
I can't seem to find where the syntax is wrong.
Additionally... Wouldn't the FIND function work within a multi-select type of column if we are trying to base the count on any rows that include that data even if there is other data in the cell (such as multiple names)?
-
Eric M Oliveira Employee
Hi Paul,
Glancing at the formula quick the section "AND(IFERROR(MONTH(DATEONLY(@cell)), 0) = 12, IFERROR(YEAR(DATEONLY(@cell)), 0) = 2019)" may be causing the error if you're receiving one, the syntax for DATEONLY(@cell) may be causing the issue because it doesn't seem to be comparing itself to anything. When utilizing the MONTH function in the example you wouldn't need the DATEONLY, written as MONTH(@cell) = 12, same with the YEAR function.
In regards to the FIND versus the HAS, you could possibly utilize the FIND, however, for this example, the HAS would be more accurate. FIND locate items in a multi-select that are similar to the given text HAS searching for an exact match of a value, including multi-contact or multi-select dropdown column cells or ranges. Returns true if found, false if not found. These topics are further outlined by the Help Center articles below.
HAS:https://help.smartsheet.com/function/has
FIND:https://help.smartsheet.com/function/find
Have a wonderful day. Thank you for contacting Smartsheet Support.
Cheers,
Eric
Smartsheet Technical Support
-
Paul Newcome ✭✭✭✭✭✭
This section is looking at the {AFID} range which is a column that is an autogenerated Date/Time stamp. DATEONLY is the correct function if we are trying to pull the date from the cell which we are for the MONTH/YEAR functions.
I actually use this very regularly and it was even suggested as a solution from someone else in Support that I use this even on regular date columns that have text and/or blank cells within the range (re: Ticket Number 03323469).
I use this very frequently without issue.
As for the FIND vs HAS...
When I included the FIND function in my solution, I did not know if it was single select or multi select. Where I disagree with your statement though is
"FIND locate items in a multi-select that are similar to the given text HAS searching for an exact match of a value"
The FIND function will not locate items that aresimilar. It will find that exact match within a string to include being case sensitive.
The HAS function will only locate cells that are an exact match, so if you are looking for "John Doe" in a multi select column, it will only locate those cells that are strictly "John Doe". If "John Doe" and Jane Doe" are both in a cell, the HAS function will not locate it.
When the range is a single select, FIND and HAS will function the exact same way when used properly.
Even after discovering the column type was a single select, I stuck with the FIND function because that is what was used in the original formula and only needed a minor tweak to get it working. Since it works the same way, I didn't see any reason to change it.
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-27T02:16:35+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-27T02:16:35+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":"