COUNTIFS Formula, Multiple Criteria

Hello!

We are trying to calculate how many days delay our projects are incurring. However we only want to count Special Conditions that have a "*" in front of it. So Special Conditions with * in front of it, summing up # of days delay in the next column. Totaling for entire column. We worked through some of the formula below but just can't seem to get it correct. Any help is appreciated!


Answers

  • David Tutwiler
    David Tutwiler Overachievers Alumni

    Just to clarify, you want to count all of the days delayed if the day selected has an * in front? Is that right?

    If that's the case I think you'll need SUMIF. Something like:

    =SUMIF([Special Conditions]:[Special Conditions], CONTAINS("*", @cell), [# of Days Delay]:[# of Days Delay])

    Here you're looking at the range Special Conditions to see if the cell contains an *, and if it does then look to the # of Days Delay column to find the number to include in the sum.

    That should do it.

  • Hi David,

    That worked for 6 out of the 7 sheets. Which to the naked eye are all set up exactly the same. The 7th sheet still pops unparseable once I put the formula in.


  • David Tutwiler
    David Tutwiler Overachievers Alumni

    That's odd. It does look correct. Is there an extra space at the end of one of your column names?

    I would try to rebuild the formula in the 7th sheet manually. So write the SUMIF( and then go click on the column Special Conditions and let it populate the true name of the column. You'll have to delete the row reference (so make [Special Conditions]7 look like [Special Conditions]:[Special Conditions]) but you'll at least know that the column names are right when you do that. I would do the same thin for the # of Days column.

    After doing that does that still give you an error?

  • Stefan
    Stefan ✭✭✭✭✭✭

    Hi @Allie Wassel,

    almost there:

    [# of Days Delay] => [# of Days of Delay]

    ;-)

    Greetings

    Stefan

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

  • David Tutwiler
    David Tutwiler Overachievers Alumni
  • Stefan
    Stefan ✭✭✭✭✭✭

    Typing errors are my hobby ;-) @David Tutwiler

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!