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

Find more posts tagged with

Accepted answers

Andrée Starå

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!

All comments

Andrée Starå

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?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

claude.ngongang80316

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

Andrée Starå

Happy to help!

Apologies for the delayed response.

Do you still need help, or have you solve it?

claude.ngongang80316

Thanks Andrée

The issue has been resolve.

Have a great day.

Cheers,

Claude

Andrée Starå

Excellent!

Glad you got it working!

Be safe and have a fantastic weekend!

Best,

Andrée

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.