COUNTIFS | 2 CRITERIA FROM SAME COLUMN | REFERENCE SHEET
Kind Sir/Madam,
Greatly appreciate any help on the following:
I created an extra sheet with formula's so I can have them displayed in a dashboard without having to add all kind of formulas and outcome of these formulas in my source sheet. My goal is to count the number of "approved" and "completed" quotations (same column) in a reference sheet.
Short crash course in Dutch:
AANVAARD means approved
AFGEROND means completed
With one criteria it works fine:
=COUNTIF({Fase}; "AFGEROND")
But when I add the second criteria, please note it has to be either the first "AANVAARD" or "AFGEROND"
I've tried to use the@cellway as was advised by Brian W inthis article:
=COUNTIFS({Fase};@cell= "AANVAARD", {Fase};@cell= "AFGEROND"))
but I get an "UNPARSEABLE" error
I also tried this:
=COUNTIFS({Fase}; "AANVAARD"; {Fase}; "AFGEROND")
but ofcourse it comes up with zero, so I added OR to the formula:
=COUNTIFS({Fase}; "AANVAARD"; OR {Fase}; "AFGEROND")
I called Smartsheet for help. Very friendly staff but they needed some time to figure out the formula and I just cannot stand it them I'm not able to figure this one out.
Thanks for the help, much appreciated.
Kind regards,
Mike
Comments
-
Nic Larsen ✭✭✭✭✭✭
-
MikeChap ✭✭
Ah man, bullseye !! thank you Nic. Would you be willing to help me out on the next one too?
Now I know the number of definitive quotations/bookings that have the status "approved" or "completed". But here's where it gets tricky (for me at least) now I have to display the amount (in Dutch "offertebedrag") of these "approved: or "completed" bookings in euros of all future bookings.
So far I came up with a formula for only 1 criteria "AANVAARD" but no luck up untill now:
=SUMIFS({Offertebedrag}; {Fase}; "AANVAARD"; {Datum boeking} >= TODAY(0); {Offertebedrag}) -
Nic Larsen ✭✭✭✭✭✭
-
Nic Larsen ✭✭✭✭✭✭
-
MikeChap ✭✭
Managed to figure it out, thanks for help. It's 01:30 AM in my country and because of your help I can now get some sleep :-) I'm a newbee to this community but it's always nice to see people help each other out. Thanks again. Take care. Bye.
-
Nic Larsen ✭✭✭✭✭✭
Perfect glad to help. This is the best place to learn. Lots of good folks here to assist.
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/46946/\")<\/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":38,"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":"