Average for both columns

RobNY2
RobNY2 ✭
edited 09/22/22 inFormulas and Functions

I have a dropdown columns with several choices and each choice represents % complete. The idea is to get the Circuit % complete for 1st and 2nd circuits columns but also It has ‘N/A” that will not count. It only count the column that doesn’t have it.

This is what I tried:

=AVG(IF([1st Circuit Status]@row = "Assigned to Local Carrier", 0.1), IF([1st Circuit Status]@row = "Circuit Delivery", 1),IF([1st Circuit Status]@row = "Circuit in Construction Phase", .8),IF([2nd Circuit Status]@row = "Assigned to Local Carrier", 0.1),IF([2nd Circuit Status]@row = "Circuit Delivery", 1),IF([2nd Circuit Status]@row = "Circuit in Construction Phase", .8)

image.png








If you could help me, I really appreciate it!

Rob

Tags:
· Share on Facebook Share on Twitter

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi@RobNY2

    If I'm understanding you correctly, you have one specific value that you want to assign as a percentage per choice, and there are two columns where these values could appear. In each row you want to AVG the two values from those two cells for a final percentage.

    If this is the case, I believe you're missing a comma from the first formula. This is the structure:

    =SUM(first value,second value) / 2

    However notice that when you swap from doing an IF statement for the 1st Circuit Status there's no comma. It should be:

    =SUM(IF([1st Circuit Status]@row = "Assigned to Local Carrier", 0.1) + IF([1st Circuit Status]@row = "Circuit Delivery", 1) + IF([1st Circuit Status]@row = "Circuit in Construction Phase", 0.8), IF([2nd Circuit Status]@row = "Assigned to Local Carrier", 0.1) + IF([2nd Circuit Status]@row = "Circuit Delivery", 1) + IF([2nd Circuit Status]@row = "Circuit in Construction Phase", 0.8)) / 2


    Since you have a much longer list, what I would suggest doing is two Nested IF statements (assuming that you'll only ever have one value per cell).

    This way you can group together all the same values that have the same output with an IF(OR statement, like so:

    IF(OR([1st Circuit Status]@row= "Contract in Process", [1st Circuit Status]@row= "Waiting for Quote", [1st Circuit Status]@row= "Waiting for Order Validation to complete", [1st Circuit Status]@row= "Cancelled", [1st Circuit Status]@row= "Requirement Review", [1st Circuit Status]@row= "Not Started", [1st Circuit Status]@row= "Out of Scope", [1st Circuit Status]@row= "Reassigned to another Carrier", [1st Circuit Status]@row= "Need to reassign to another Carrier", [1st Circuit Status]@row= "Addendum 37"), 0,


    For a full formula, you could do something like this:

    =SUM(

    IF(OR([1st Circuit Status]@row= "Contract in Process", [1st Circuit Status]@row= "Waiting for Quote", [1st Circuit Status]@row= "Waiting for Order Validation to complete", [1st Circuit Status]@row= "Cancelled", [1st Circuit Status]@row= "Requirement Review", [1st Circuit Status]@row= "Not Started", [1st Circuit Status]@row= "Out of Scope", [1st Circuit Status]@row= "Reassigned to another Carrier", [1st Circuit Status]@row= "Need to reassign to another Carrier", [1st Circuit Status]@row= "Addendum 37"), 0, IF([1st Circuit Status]@row= "Started", .01, IF([1st Circuit Status]@row= "Waiting to assign to local carrier", .05, IF([1st Circuit Status]@row= "Circuit Order in process", .04, IF([1st Circuit Status]@row= "Assigned to Local Carrier", .10, IF([1st Circuit Status]@row= "Circuit in Design Phase", .6, IF([1st Circuit Status]@row= "Circuit Site Survey in Progress", .65, IF([1st Circuit Status]@row= "Circuit Site Survey Complete",.70, IF([1st Circuit Status]@row= "LCON Waiting Carrier to provide Design", .71, IF([1st Circuit Status]@row= "On Hold", .50, IF([1st Circuit Status]@row= "Waiting for LCON to Approve Site Survey", .73, IF([1st Circuit Status]@row= "Circuit in Construction Phase", .8, IF([1st Circuit Status]@row= "Circuit installed waiting carrier to deliver", .99, IF(OR([1st Circuit Status]@row= "Circuit Delivered", [1st Circuit Status]@row= "Use local Current Circuits"), 1),

    IF(OR([2nd Circuit Status]@row= "Contract in Process", [2nd Circuit Status]@row= "Waiting for Quote", [2nd Circuit Status]@row= "Waiting for Order Validation to complete", [2nd Circuit Status]@row= "Cancelled", [2nd Circuit Status]@row= "Requirement Review", [2nd Circuit Status]@row= "Not Started", [2nd Circuit Status]@row= "Out of Scope", [2nd Circuit Status]@row= "Reassigned to another Carrier", [2nd Circuit Status]@row= "Need to reassign to another Carrier", [2nd Circuit Status]@row= "Addendum 37"), 0, IF([2nd Circuit Status]@row= "Started", .01, IF([2nd Circuit Status]@row= "Waiting to assign to local carrier", .05, IF([2nd Circuit Status]@row= "Circuit Order in process", .04, IF([2nd Circuit Status]@row= "Assigned to Local Carrier", .10, IF([2nd Circuit Status]@row= "Circuit in Design Phase", .6, IF([2nd Circuit Status]@row= "Circuit Site Survey in Progress", .65, IF([2nd Circuit Status]@row= "Circuit Site Survey Complete",.70, IF([2nd Circuit Status]@row= "LCON Waiting Carrier to provide Design", .71, IF([2nd Circuit Status]@row= "On Hold", .50, IF([2nd Circuit Status]@row= "Waiting for LCON to Approve Site Survey", .73, IF([2nd Circuit Status]@row= "Circuit in Construction Phase", .8, IF([2nd Circuit Status]@row= "Circuit installed waiting carrier to deliver", .99, IF(OR([2nd Circuit Status]@row= "Circuit Delivered", [2nd Circuit Status]@row= "Use local Current Circuits"), 1)

    /2


    As a final note, make sure that any time you have a quotation mark in a formula it's straight up and down like so: "

    I notice that some of your quotes are curvy (” versus ") which would result in an error in Smartsheet.

    Cheers,

    Genevieve

    · Share on Facebook Share on Twitter

Answers

  • Leibel S
    Leibel S ✭✭✭✭✭✭

    @RobNY2

    See below option:

    =SUM(IF([1st Circuit Status]@row = "Assigned to Local Carrier", 0.1) + IF([1st Circuit Status]@row = "Circuit Delivery", 1) + IF([1st Circuit Status]@row = "Circuit in Construction Phase", 0.8) + IF([2nd Circuit Status]@row = "Assigned to Local Carrier", 0.1) + IF([2nd Circuit Status]@row = "Circuit Delivery", 1) + IF([2nd Circuit Status]@row = "Circuit in Construction Phase", 0.8)) / 2

    · Share on Facebook Share on Twitter
  • RobNY2
    RobNY2 ✭
    edited 09/14/22

    Leibel

    I am getting UNPARSEABLE.

    The 1st formula below is what you gave me and it works:

    =SUM(IF([1st Circuit Status]@row = "Assigned to Local Carrier", 0.1) + IF([1st Circuit Status]@row = "Circuit Delivery", 1) + IF([1st Circuit Status]@row = "Circuit in Construction Phase", 0.8) + IF([2nd Circuit Status]@row = "Assigned to Local Carrier", 0.1) + IF([2nd Circuit Status]@row = "Circuit Delivery", 1) + IF([2nd Circuit Status]@row = "Circuit in Construction Phase", 0.8)) / 2

    The 2nd formula below is what you gave me I just changed what is between" "and removed some spaces. It didn't work but your formula above without the spaces also worked, so the problem it is not removing the spaces:

    =SUM(IF([1st Circuit Status]@row="Assigned to Local Carrier",0.1)+IF([1st Circuit Status]@row="Circuit Delivery",1)+IF([1st Circuit Status]@row ="Circuit in Construction Phase",0.8)+IF([2nd Circuit Status]@row="Assigned to Local Carrier",0.1)+IF([2nd Circuit Status]@row ="Circuit Delivery",1)+IF([2nd Circuit Status]@row="Circuit in Construction Phase",0.8))/2

    This 3rd formula below is what I really need to insert in the cell. I removed the spaces because it is a big formula. Basically I copied what it is for "1st Circuit Status" and at the end for the formula "1st Circuit Status" I added the same formula but I changed to "2nd Circuit Status" and it didn't work:

    =SUM(IF([1电路状态]@row =“合同过程”,0)+IF([1st Circuit Status]@row=“Waiting for Quote”,0)+IF([1st Circuit Status]@row=“Waiting for Order Validation to complete”,0)+IF([1st Circuit Status]@row=“Cancelled”,0)+IF([1st Circuit Status]@row=“Requirement Review”,0)+IF([1st Circuit Status]@row=“Not Started”,0)+IF([1st Circuit Status]@row=“Out of Scope”,0)+IF([1st Circuit Status]@row=“Started”,.01)+IF([1st Circuit Status]@row=“Waiting to assign to local carrier”,.05)+IF([1st Circuit Status]@row=“Circuit Order in process”,.04)+IF([1st Circuit Status]@row=“ Reassigned to another Carrier”,0)+IF([1st Circuit Status]@row=“Assigned to Local Carrier”,.10)+IF([1st Circuit Status]@row=“Circuit in Design Phase”,.6)+IF([1st Circuit Status]@row=“Circuit Site Survey in Progress”,.65)+IF([1st Circuit Status]@row=“Circuit Site Survey Complete”,.70)+IF([1st Circuit Status]@row=“LCON Waiting Carrier to provide Design”,.71)+IF([1st Circuit Status]@row=“On Hold”,.50)+IF([1st Circuit Status]@row=“Waiting for LCON to Approve Site Survey”,.73)+IF([1st Circuit Status]@row=“Circuit in Construction Phase”,.8)+IF([1st Circuit Status]@row=“Circuit installed waiting carrier to deliver”,.99)+IF([1st Circuit Status]@row=“Circuit Delivered”,1)+IF([1st Circuit Status]@row=“Use local Current Circuits”,1)+IF([1st Circuit Status]@row=“Need to reassign to another Carrier”,0)+IF([1st Circuit Status]@row=“Addendum 37”,0)+如果([第二电路状态]@row =“合同过程”,0)+IF([2nd Circuit Status]@row=“Waiting for Quote”,0)+IF([2nd Circuit Status]@row=“Waiting for Order Validation to complete”,0)+IF([2nd Circuit Status]@row=“Cancelled”,0)+IF([2nd Circuit Status]@row=“Requirement Review”,0)+IF([2nd Circuit Status]@row=“Not Started”,0)+IF([2nd Circuit Status]@row=“Out of Scope”,0)+IF([2nd Circuit Status]@row=“Started”,.01)+IF([2nd Circuit Status]@row=“Waiting to assign to local carrier”,.05)+IF([2nd Circuit Status]@row=“Circuit Order in process”,.04)+IF([2nd Circuit Status]@row=“ Reassigned to another Carrier”,0)+IF([2nd Circuit Status]@row=“Assigned to Local Carrier”,.10)+IF([2nd Circuit Status]@row=“Circuit in Design Phase”,.6)+IF([2nd Circuit Status]@row=“Circuit Site Survey in Progress”,.65)+IF([2nd Circuit Status]@row=“Circuit Site Survey Complete”,.70)+IF([2nd Circuit Status]@row=“LCON Waiting Carrier to provide Design”,.71)+IF([2nd Circuit Status]@row=“On Hold”,.50)+IF([2nd Circuit Status]@row=“Waiting for LCON to Approve Site Survey”,.73)+IF([2nd Circuit Status]@row=“Circuit in Construction Phase”,.8)+IF([2nd Circuit Status]@row=“Circuit installed waiting carrier to deliver”,.99)+IF([2nd Circuit Status]@row=“Circuit Delivered”,1)+IF([2nd Circuit Status]@row=“Use local Current Circuits”,1)+IF([2nd Circuit Status]@row=“Need to reassign to another Carrier”,0)+IF([2nd Circuit Status]@row=“Addendum 37”,0))/2

    Below are the choices that I have in the dropdown menu that I need to assign the %

    image.png


    Rob

    

    · Share on Facebook Share on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi@RobNY2

    If I'm understanding you correctly, you have one specific value that you want to assign as a percentage per choice, and there are two columns where these values could appear. In each row you want to AVG the two values from those two cells for a final percentage.

    If this is the case, I believe you're missing a comma from the first formula. This is the structure:

    =SUM(first value,second value) / 2

    However notice that when you swap from doing an IF statement for the 1st Circuit Status there's no comma. It should be:

    =SUM(IF([1st Circuit Status]@row = "Assigned to Local Carrier", 0.1) + IF([1st Circuit Status]@row = "Circuit Delivery", 1) + IF([1st Circuit Status]@row = "Circuit in Construction Phase", 0.8), IF([2nd Circuit Status]@row = "Assigned to Local Carrier", 0.1) + IF([2nd Circuit Status]@row = "Circuit Delivery", 1) + IF([2nd Circuit Status]@row = "Circuit in Construction Phase", 0.8)) / 2


    Since you have a much longer list, what I would suggest doing is two Nested IF statements (assuming that you'll only ever have one value per cell).

    This way you can group together all the same values that have the same output with an IF(OR statement, like so:

    IF(OR([1st Circuit Status]@row= "Contract in Process", [1st Circuit Status]@row= "Waiting for Quote", [1st Circuit Status]@row= "Waiting for Order Validation to complete", [1st Circuit Status]@row= "Cancelled", [1st Circuit Status]@row= "Requirement Review", [1st Circuit Status]@row= "Not Started", [1st Circuit Status]@row= "Out of Scope", [1st Circuit Status]@row= "Reassigned to another Carrier", [1st Circuit Status]@row= "Need to reassign to another Carrier", [1st Circuit Status]@row= "Addendum 37"), 0,


    For a full formula, you could do something like this:

    =SUM(

    IF(OR([1st Circuit Status]@row= "Contract in Process", [1st Circuit Status]@row= "Waiting for Quote", [1st Circuit Status]@row= "Waiting for Order Validation to complete", [1st Circuit Status]@row= "Cancelled", [1st Circuit Status]@row= "Requirement Review", [1st Circuit Status]@row= "Not Started", [1st Circuit Status]@row= "Out of Scope", [1st Circuit Status]@row= "Reassigned to another Carrier", [1st Circuit Status]@row= "Need to reassign to another Carrier", [1st Circuit Status]@row= "Addendum 37"), 0, IF([1st Circuit Status]@row= "Started", .01, IF([1st Circuit Status]@row= "Waiting to assign to local carrier", .05, IF([1st Circuit Status]@row= "Circuit Order in process", .04, IF([1st Circuit Status]@row= "Assigned to Local Carrier", .10, IF([1st Circuit Status]@row= "Circuit in Design Phase", .6, IF([1st Circuit Status]@row= "Circuit Site Survey in Progress", .65, IF([1st Circuit Status]@row= "Circuit Site Survey Complete",.70, IF([1st Circuit Status]@row= "LCON Waiting Carrier to provide Design", .71, IF([1st Circuit Status]@row= "On Hold", .50, IF([1st Circuit Status]@row= "Waiting for LCON to Approve Site Survey", .73, IF([1st Circuit Status]@row= "Circuit in Construction Phase", .8, IF([1st Circuit Status]@row= "Circuit installed waiting carrier to deliver", .99, IF(OR([1st Circuit Status]@row= "Circuit Delivered", [1st Circuit Status]@row= "Use local Current Circuits"), 1),

    IF(OR([2nd Circuit Status]@row= "Contract in Process", [2nd Circuit Status]@row= "Waiting for Quote", [2nd Circuit Status]@row= "Waiting for Order Validation to complete", [2nd Circuit Status]@row= "Cancelled", [2nd Circuit Status]@row= "Requirement Review", [2nd Circuit Status]@row= "Not Started", [2nd Circuit Status]@row= "Out of Scope", [2nd Circuit Status]@row= "Reassigned to another Carrier", [2nd Circuit Status]@row= "Need to reassign to another Carrier", [2nd Circuit Status]@row= "Addendum 37"), 0, IF([2nd Circuit Status]@row= "Started", .01, IF([2nd Circuit Status]@row= "Waiting to assign to local carrier", .05, IF([2nd Circuit Status]@row= "Circuit Order in process", .04, IF([2nd Circuit Status]@row= "Assigned to Local Carrier", .10, IF([2nd Circuit Status]@row= "Circuit in Design Phase", .6, IF([2nd Circuit Status]@row= "Circuit Site Survey in Progress", .65, IF([2nd Circuit Status]@row= "Circuit Site Survey Complete",.70, IF([2nd Circuit Status]@row= "LCON Waiting Carrier to provide Design", .71, IF([2nd Circuit Status]@row= "On Hold", .50, IF([2nd Circuit Status]@row= "Waiting for LCON to Approve Site Survey", .73, IF([2nd Circuit Status]@row= "Circuit in Construction Phase", .8, IF([2nd Circuit Status]@row= "Circuit installed waiting carrier to deliver", .99, IF(OR([2nd Circuit Status]@row= "Circuit Delivered", [2nd Circuit Status]@row= "Use local Current Circuits"), 1)

    /2


    As a final note, make sure that any time you have a quotation mark in a formula it's straight up and down like so: "

    I notice that some of your quotes are curvy (” versus ") which would result in an error in Smartsheet.

    Cheers,

    Genevieve

    · Share on Facebook Share on Twitter
  • RobNY2
    RobNY2 ✭

    Genevieve


    Thank you very much! I appreciate your help!


    Rob

    · Share on Facebook Share on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
Try this - =\"We are at \" + [% closed rate]@row * 100 + \"% closed rate on ticket status for the month of \"+[Month]@row.<\/p>"},{"commentID":390301,"body":"

I figured it out! Updated formula to get the 2 decimal places as well.<\/p>

=\"We are at \" + IFERROR(ROUND([% closed rate]@row * 100, 2), \"//www.santa-greenland.com/community/discussion/comment/\") + \"% closed rate on ticket status for the month of \" + Month@row<\/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":108861,"type":"question","name":"IF\/AND Formula","excerpt":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I have not received an invoice and the invoice due date is within 30 days. I know I'm close since I've gone from \"unparsable\" to \"incorrect…","snippet":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I…","categoryID":322,"dateInserted":"2023-08-11T16:27:44+00:00","dateUpdated":null,"dateLastComment":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":120231,"lastUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":54,"score":null,"hot":3383549849,"url":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","format":"Rich","lastPost":{"discussionID":108861,"commentID":390268,"name":"Re: IF\/AND Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390268#Comment_390268","dateInserted":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+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-11T17:48:48+00:00","dateAnswered":"2023-08-11T17:12:43+00:00","acceptedAnswers":[{"commentID":390261,"body":"

You were, indeed, very close.<\/p>

=IF([Invoice Received?]@row = 0, IF(AND([Renewal Date]@row >= TODAY(), [Renewal Date]@row <= TODAY(+30)), \"Red\"))<\/p>"},{"commentID":390264,"body":"

\n \n https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula\n <\/a>\n<\/div>\n

It looks like you forgot to close out the AND <\/strong>funtion. Try this<\/p>

=IF([Invoice Received?]@row = 0, \"Green\", IF(AND([Renewal Date]@row >= TODAY(0), [Renewal Date]@row <= TODAY(+30)), \"Red\", \"Yellow\"))<\/code><\/p>

Hope this helps!<\/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":108832,"type":"question","name":"How do I create a IF(AND formula combining multiple cross references?","excerpt":"In this screenshot I am sharing my rollup data sheet. Of the 6 Total Amex CB Count, I am trying to determine how many of those each of our Branches has but I keep getting different errors using formula: =COUNTIFS({Dispute & Chargeback intake sheet branch}, CONTAINS([Data Points]32, @cell), IF(AND({Dispute & Chargeback…","snippet":"In this screenshot I am sharing my rollup data sheet. Of the 6 Total Amex CB Count, I am trying to determine how many of those each of our Branches has but I keep getting…","categoryID":322,"dateInserted":"2023-08-10T23:46:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-11T17:12:56+00:00","insertUserID":140084,"insertUser":{"userID":140084,"name":"Krystal Garcia","url":"https:\/\/community.smartsheet.com\/profile\/Krystal%20Garcia","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hEDXYe8sIOY!ZF9XGSNgaPQ!0Bh2ICPdBzl","dateLastActive":"2023-08-11T17:23:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":140084,"lastUser":{"userID":140084,"name":"Krystal Garcia","url":"https:\/\/community.smartsheet.com\/profile\/Krystal%20Garcia","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hEDXYe8sIOY!ZF9XGSNgaPQ!0Bh2ICPdBzl","dateLastActive":"2023-08-11T17:23:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":43,"score":null,"hot":3383486357,"url":"https:\/\/community.smartsheet.com\/discussion\/108832\/how-do-i-create-a-if-and-formula-combining-multiple-cross-references","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108832\/how-do-i-create-a-if-and-formula-combining-multiple-cross-references","format":"Rich","tagIDs":[207,219,254,344,440],"lastPost":{"discussionID":108832,"commentID":390262,"name":"Re: How do I create a IF(AND formula combining multiple cross references?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390262#Comment_390262","dateInserted":"2023-08-11T17:12:56+00:00","insertUserID":140084,"insertUser":{"userID":140084,"name":"Krystal Garcia","url":"https:\/\/community.smartsheet.com\/profile\/Krystal%20Garcia","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hEDXYe8sIOY!ZF9XGSNgaPQ!0Bh2ICPdBzl","dateLastActive":"2023-08-11T17:23:36+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/9UG82HSGCS9G\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-11T17:23:34+00:00","dateAnswered":"2023-08-11T00:00:03+00:00","acceptedAnswers":[{"commentID":390143,"body":"

I think I am following what you are attempting to do. Try this:<\/p>

COUNTIFS({Dispute & Chargeback intake sheet branch}, CONTAINS([Data Points]32, @cell), {Dispute & Chargeback Intake Sheet Type of CB}, \"AMEX\")<\/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":207,"urlcode":"Sales","name":"Sales"},{"tagID":219,"urlcode":"Sheets","name":"Sheets"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":344,"urlcode":"it-operations","name":"IT & Operations"},{"tagID":440,"urlcode":"project-management","name":"Project Management"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions

  • Is there a formula to show as %

    What I am trying to do is have my overview brought in automatically each month so it doesn't have to manually be typed in. The struggle I'm having is it wants to bring in as a decimal instead of a %. Below is the formula I am currently using. Is there a way to make it show as % ="We are at " + [% closed rate]@row + "…

  • IF/AND Formula

    Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I have not received an invoice and the invoice due date is within 30 days. I know I'm close since I've gone from "unparsable" to "incorrect…

  • How do I create a IF(AND formula combining multiple cross references?

    In this screenshot I am sharing my rollup data sheet. Of the 6 Total Amex CB Count, I am trying to determine how many of those each of our Branches has but I keep getting different errors using formula: =COUNTIFS({Dispute & Chargeback intake sheet branch}, CONTAINS([Data Points]32, @cell), IF(AND({Dispute & Chargeback…