COUNTIFS( ) / IF(ADND() formula
Good Day!
I am having an error message when trying with the formula below:
I am trying to check a box if 2 conditions are met. project may be either in design phase (Analyse/Conception) or Execution. If the first condition is met, and the second (date) are met, the box is checked.
=IF(AND({Phase}@row = "Analyse/Conception"; [Production DC Analyste]@cell < >" "); 1;0))
The second issue is with the case below:
I would like to count the number of project in Execution as well as the number of project in Design.
Received, delivered and in progress.
=COUNTIFS({Phase}; [Colonne principale]@row; {Date d'ouverture})
Thanks in advance,
Claude
Best Answer
-
Hi Claude,
Do you want to use the formula in the sheet or cross-sheet?
Question 1
Try something like this.
=IF(AND(Phase@row = "Analyse/Conception"; [Production DC Analyste]@row <> ""); 1; 0)
The same version but with the below changes for your and others convenience.
=IF(AND(Phase@row = "Analyse/Conception", [Production DC Analyste]@row <> ""), 1, 0)
Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.
Did that work?
Question 2
Do you want to count in a row or column?
Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some more screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers
-
Hi Claude,
Do you want to use the formula in the sheet or cross-sheet?
Question 1
Try something like this.
=IF(AND(Phase@row = "Analyse/Conception"; [Production DC Analyste]@row <> ""); 1; 0)
The same version but with the below changes for your and others convenience.
=IF(AND(Phase@row = "Analyse/Conception", [Production DC Analyste]@row <> ""), 1, 0)
Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.
Did that work?
Question 2
Do you want to count in a row or column?
Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some more screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Good Day Andrée,
Question 1 worked, Thanks very much.
For Question 2,
Below are the screen shot of my workflow. Our customers send us a request to provide them our services. We design the solution and send back a proposal. If they approve, they submit a PO and we execute the order. I would like to count the number of request received within each phase (Design and Construction), the number of request completed and the number in progress.
I also hit another issue, as you can see in the column test. I am having the following message: ''INVALID OPERATION''. Since I receive 2 major date from my clients, ''Date requise DC'' and ''Date Requise MES'' respectively Date required to receive proposal for design phase and date required for implementation for Execution phase, I would like to use the ''RYG'' symbols to provide a health status to my team. According to the phase, if the required date is 10 Days from today'sdate, the project is Red. If the required date is between 11 and 20 days from today's date, the project is ''Yellow''. If the requirred date is over 20 days from today's date, the project is ''Green''.
I am still looking into, the first portion (the design phase)
=IF(AND(Phase@row = "Analyse/Conception"; [Date Requise DC]@row > TODAY(20)); "Vert"; IF(AND(Phase@row = "Analyse/Conception"; [Date Requise DC]@row > =TODAY(10)); "Jaune"; IF(AND([Date Requise DC]@row <= TODAY(19)); "Jaune"; IF(AND(Phase@row = "Analyse/Conception"; [Date Requise DC]@row = TODAY(0)); "Rouge"; ""))))
Thanks,
Claude
-
Happy to help!
Apologies for the delayed response.
Do you still need help, or have you solve it?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Thanks Andrée
The issue has been resolve.
Have a great day.
Cheers,
Claude
-
Excellent!
Glad you got it working!
Be safe and have a fantastic weekend!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!