Need help with a formula
I am trying to calculate a percentage of items that meet a specified criteria during a particular period. I can calculate the percentage correctly [=COUNTIF(A:A>3)/COUNT(B:B)] but can't figure out how to add the filter for the period (it's a "Month" column).
=IF(Month:Month=7(COUNTIF(A:A,">3")/(COUNT(B:B),"") does not work - it returns either an UNPARSEABLE or INVALID OPERATION error. I get an invalid operation errror with just the IF statement (IF Month:Month =7, "Yep", "Nope"). So I'm assuming IF is not the formula to use - but I do not know how to find the correct option. Thanks in advance for suggestions!
Answers
-
jpaul ✭✭✭✭
are your column headings actually "A" and "B"?
Your IF statement has a typo in it so that might be why it's not running properly. Try Pasting the below in and see if that works.
=IF(Month:Month =7, "Yep", "Nope")
Try using the below formula to fix your bigger question.
=(COUNTIF([A]:[A], 7)) / COUNT([B]:[B])
-
Ldonoghue ✭✭
Hi and thanks for your response! The typo was just in the example; the syntax is correct in the actual sheet. I think the issue is that "IF" is not the right formula to use. The countif/count part works - it's adding the IF statement that doesn't.
-
Hollie Green ✭✭✭✭✭
If I am understanding your sheet layout correctly the below should work.
COUNTIFS(A:A,>3,Month:Month,=7)/(COUNTIF(Month:Month,=7)
-
Ldonoghue ✭✭
Thank you for responding Hollie! That did not work but it leaves out part of the equation.
I want to calculate the percentage of records that achieved a certain score in a given period. So I'm saying for this period (e.g. "June"), count all the records scoring ">3" (COUNTIF) and divide that number by the total number of records evaluated in that period (COUNT). I tried modifying your suggestion to include a COUNTIFS at the end of the formula also, but either I did it incorrectly or it's still not the right formula. Here's how it looks...
=COUNTIFS({2023-2024 Call Monitoring Archive Range 2}">3",{{2023-2024 Call Monitoring Archive Month}=7)/COUNTIFS({2023-2024 Call Monitoring Archive Range 5}<>"",{{2023-2024 Call Monitoring Archive Month}=7)
-
jpaul ✭✭✭✭
Hollie, try the below
=COUNTIFS({2023-2024 Call Monitoring Archive Range 2},>3,{2023-2024 Call Monitoring Archive Month}=7)/COUNTIFS({2023-2024 Call Monitoring Archive Range 5}<>"",{2023-2024 Call Monitoring Archive Month}=7)
-
Ldonoghue ✭✭
Thank you jpaul - but that returns #INVALID OPERATION.
-
jpaul ✭✭✭✭
Are you cross referencing a different sheet or are all of these on the same sheet?
-
Paul Newcome ✭✭✭✭✭✭
There are a few syntax issues that need to be cleaned up first. Try this:
=COUNTIFS({2023-2024 Call Monitoring Archive Range 2},@cell>3,{2023-2024 Call Monitoring Archive Month}, @cell=7)/COUNTIFS({2023-2024 Call Monitoring Archive Range 5}, @cell<>"",{2023-2024 Call Monitoring Archive Month}, @cell=7)
-
Ldonoghue ✭✭
Returns #Incorrect Argument :( Thank you for the syntax corrections nonetheless - it is helpful for me when I'm trying to find missing or misplaced commas, etc!
-
Paul Newcome ✭✭✭✭✭✭
Double check your ranges. They should all be the same size/shape. In this instance, most likely entire columns by clicking on the appropriate column header when setting up.
-
Ldonoghue ✭✭
Thanks! The ranges are all entire columns.
-
Paul Newcome ✭✭✭✭✭✭
Are you able to provide a screenshot of the formula actually open in the sheet (as if you were going to edit it)?
-
Curtis Lange Employee
Hello Ldonoghue.
I think I understand what you're trying to do. I broke the formula into two parts.
First we need to get the number of entries that have a score greater than 3 for the month 7.
=COUNTIFS(Month:Month, =7, Score:Score, >3)
Once we have that value we then need to divide that by the number of entries we have for that month.
=INDEX(Total:Total, MATCH(7, Month:Month, 0))
I then combined the formulas together. I added extra paranthesis to ensure order of operations.
=(COUNTIFS(Month:Month, =7, Score:Score, >3)) / (INDEX(Total:Total, MATCH(7, Month:Month, 0)))
Let me know if this works for you.
-
Ldonoghue ✭✭
Thank you for your responses and suggestions@Curtis Langeand@Paul Newcome! I continue to run into issues - I think in the case of the INDEX formula, possibly because of the type of data in the column or because it's referencing a different sheet. I think I've found a workaround that will get me what I need, but you helped get me on the path! Sorry I didn't post this sooner.
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":26,"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":25,"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":"