Cant get a countsif formula with multiple phase helper sections to work.

Options

Hi I am trying to count project health indicators ,

But only i certain sections of my project plan.

I can get a basic countsif formula to work:

=COUNTIFS({Project Plan - Health}, =Metadata@row) + 0.0001


I can also get it to work with just one section :

=COUNTIFS({Project Plan - Health}, =Metadata@row, {Project Plan - Phase Helper}, ="Milestones") + 0.0001

But when i try to get selected data i fail!

I tried this :

=COUNTIFS({Project Plan - Health}, =Metadata@row, {Project Plan - Phase Helper}, ="Milestones, and {Project Plan - Phase Helper}, ="ORDERING" and {Project Plan - Phase Helper}, ="Customer Communications”) + 0.0001

What am i doing wrong? Can anyone help please?


Thanks


Paul

Tags:

Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    edited 02/11/22
    Options

    Hi @Someg ,


    You're close! It looks like you my actually be looking to use an OR statement, though. Is it true that your logic is:

    Count the number of times Project Plan - Health is equal to Metadata on this row AND Project Plan - Phase Helper is EITHER (Milestones, ORDERING, or Customer Communications), and then add 0.0001

    If so, it would look like this:

    =COUNTIFS({Project Plan - Health}, Metadata@row, {Project Plan - Phase Helper}, OR(@cell="Milestones", =@cell"ORDERING", @cell="Customer Communications”)) + 0.0001


    Hope this helps. Let me know if it works for you!


    Best,

    Heather

  • Someg
    Someg ✭✭
    Options

    Hi Heather


    thanks for the response - unfortunately that still doesn't work.

    I'm no looking to reference a single cell but multiple cells within a phase of the project - so milestones has approx 15 entries.

    thanks


    Paul

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Options

    @Someg It may be helpful to see a screen shot of your source sheet that is being referenced. Are you able to do that (with all sensitive information blocked out, of course)?

    Otherwise, we can carve away at it by asking a bunch of questions. Is your {Project Plan - Phase Helper} range one column or more than one?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!