FORMULA - COUNTIF WITH 2 ARGUMENTS
Hi,
In sheet summary I am trying to count total number of 'EVENT CATEGORY' that is 'STATUS'
The formula I have tried is =COUNTIF([Event category]:[Event category], "Webinar" [status]:[status], "complete")
Both columns are dropdown single select if that makes any difference. I am able to count by event category but adding the second criteria isn't working for me - I'm sure this is probably something simple, can anyone help?
Thanks,
Katrina
Best Answer
-
John Jonassen ✭✭✭✭I think you are almost there@Katrina Savage
Try this:
=COUNTIFS([Event category]:[Event category], "Webinar",[status]:[status], "complete")
(updated to change to COUNTIFS and added the comma in between "Webinar" and [status]:[status])
Answers
-
John Jonassen ✭✭✭✭
I think you are almost there@Katrina Savage
Try this:
=COUNTIFS([Event category]:[Event category], "Webinar",[status]:[status], "complete")
(updated to change to COUNTIFS and added the comma in between "Webinar" and [status]:[status])
-
Yes it works!! Thank you for taking the time to respond!
Help Article Resources
Categories
=IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), @cell = \"Canceled\"), \"Canceled\", IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), OR(@cell = \"Complete\", @cell = \"Canceled\", @cell = \"On Hold\")), \"Complete\", IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), @cell = \"Not Started\"), \"Not Started\", IF(COUNTIFS(CHLDREN(), @cell = \"Upcoming\")> 0, \"Upcoming\", \"In Progress\"))))<\/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":219,"urlcode":"Sheets","name":"Sheets"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":108218,"type":"question","name":"Help with \"AND\" formula","excerpt":"I'm using this formula where I want to generate the average complexity score for any projects in the year 2019 where the product is ESP. However, when I use this formula I'm getting an \"Invalid Data type\". I've confirmed all references are correct. Anyone have ideas? =AVERAGEIF(AND({Complexity Year}, \"2019\", {Product},…","snippet":"I'm using this formula where I want to generate the average complexity score for any projects in the year 2019 where the product is ESP. However, when I use this formula I'm…","categoryID":322,"dateInserted":"2023-07-27T20:23:56+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T12:47:12+00:00","insertUserID":156865,"insertUser":{"userID":156865,"name":"Katie S.","url":"https:\/\/community.smartsheet.com\/profile\/Katie%20S.","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!xWDX8YmJLfc!pKNXqRG2rHs!yB3MdbY6fmJ","dateLastActive":"2023-07-28T12:44:57+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":156865,"lastUser":{"userID":156865,"name":"Katie S.","url":"https:\/\/community.smartsheet.com\/profile\/Katie%20S.","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!xWDX8YmJLfc!pKNXqRG2rHs!yB3MdbY6fmJ","dateLastActive":"2023-07-28T12:44:57+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":33,"score":null,"hot":3381039668,"url":"https:\/\/community.smartsheet.com\/discussion\/108218\/help-with-and-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108218\/help-with-and-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108218,"commentID":387766,"name":"Re: Help with \"AND\" formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387766#Comment_387766","dateInserted":"2023-07-28T12:47:12+00:00","insertUserID":156865,"insertUser":{"userID":156865,"name":"Katie S.","url":"https:\/\/community.smartsheet.com\/profile\/Katie%20S.","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!xWDX8YmJLfc!pKNXqRG2rHs!yB3MdbY6fmJ","dateLastActive":"2023-07-28T12:44:57+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-28T12:46:42+00:00","dateAnswered":"2023-07-27T22:53:31+00:00","acceptedAnswers":[{"commentID":387693,"body":"
AVERAGEIF and AND do not play well together. Try this one, it collects all values that meet your criteria and then averages them. I am assuming {Complexity Score New} is the range with the values you would like to average.<\/p>
=AVG(COLLECT({Complexity Score New}, {Complexity Year}, \"2019\", {Product}, \"ESP\"))<\/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":108226,"type":"question","name":"COUNTIF and OR or IF Formula","excerpt":"I'm looking to use COUNTIF to calculate if something is the right date and then if it has meets one of the following criteria (using references). My current formula looks like this: =COUNTIFS({Despatch Report Date}, =Date8, {Despatch Report Lot Status}, \"Q\", {Despatch Report WO Status}, <99 I basically want to count if its…","snippet":"I'm looking to use COUNTIF to calculate if something is the right date and then if it has meets one of the following criteria (using references). My current formula looks like…","categoryID":322,"dateInserted":"2023-07-27T21:30:27+00:00","dateUpdated":null,"dateLastComment":"2023-07-27T22:39:47+00:00","insertUserID":146974,"insertUser":{"userID":146974,"name":"Jack Parry","url":"https:\/\/community.smartsheet.com\/profile\/Jack%20Parry","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-27T22:59:44+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":156297,"lastUser":{"userID":156297,"name":"Pat Canning","url":"https:\/\/community.smartsheet.com\/profile\/Pat%20Canning","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/5XYNMD97JXRY\/nD28D8BFM2BGU.jpg","dateLastActive":"2023-07-27T23:12:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":33,"score":null,"hot":3380991614,"url":"https:\/\/community.smartsheet.com\/discussion\/108226\/countif-and-or-or-if-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108226\/countif-and-or-or-if-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108226,"commentID":387692,"name":"Re: COUNTIF and OR or IF Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387692#Comment_387692","dateInserted":"2023-07-27T22:39:47+00:00","insertUserID":156297,"insertUser":{"userID":156297,"name":"Pat Canning","url":"https:\/\/community.smartsheet.com\/profile\/Pat%20Canning","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/5XYNMD97JXRY\/nD28D8BFM2BGU.jpg","dateLastActive":"2023-07-27T23:12:31+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-27T22:59:50+00:00","dateAnswered":"2023-07-27T22:39:47+00:00","acceptedAnswers":[{"commentID":387692,"body":"
Does this work?<\/p>
![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/2UZI5G6FRBIB\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
