Help on formula: Countifs with multiple columns and criteria

Grace

Hi,

So I've been working on a formula but couldn't get it right. I wanted to count the number of my projects that are Active (on-time) and Active (delayed).

I get the on-time count correctly but for the delayed ones I always get a plus 1 in the results. Here's the formula I use (btw the stages represents that a project is active):

Active (on-time)

=COUNTIFS(Progress:Progress, "on-time", Stage:Stage, "planning") + COUNTIFS(Progress:Progress, "on-time", Stage:Stage, "strategy") + COUNTIFS(Progress:Progress, "on-time", Stage:Stage, "tender") + COUNTIFS(Progress:Progress, "on-time", Stage:Stage, "evaluation") + COUNTIFS(Progress:Progress, "on-time", Stage:Stage, "contract")

Active (delayed)

=COUNTIFS(Progress:Progress, "delayed", Stage:Stage, "planning") + COUNTIFS(Progress:Progress, "delayed", Stage:Stage, "strategy") + COUNTIFS(Progress:Progress, "delayed", Stage:Stage, "tender") + COUNTIFS(Progress:Progress, "delayed", Stage:Stage, "evaluation") + COUNTIFS(Progress:Progress, "delayed", Stage:Stage, "contract")

Can anybody please check and advise what's the correct formula I should use?

Kind regards,

Grace

Shaine Greenwood

Hi Grace,

Based on what you're wanting your formulas to count, the syntax of both of them looks correct.

How are you comparing results to know that your "delayed" always has an incorrect +1 in the results? Are you manually counting and then checking against the formula? Are you using a feature in Smartsheet (besides formulas) to try and manually count?

To troubleshoot this, here's what I would do:

1. Disable any filters on your sheet by clicking the filter button in the toolbar
2. Break your delayed COUNTIFS down piece-by-piece. Start with just using a =COUNTIFS(Progress:Progress, "delayed", Stage:Stage, "planning") and see how your results compare. 
3. Repeat this for each section of your formula.

Grace

Hi Shaine,

Yes, I am manually counting and then checking against the formula. I tried to enter the delayed countifs one by one but I'm getting the same results. I have one project under Strategy stage with progress as on-time and I have found that this is where the plus one is coming from.

Here's the formula:

=COUNTIFS(Progress:Progress, "delayed", Stage:Stage, "planning") + COUNTIFS(Progress:Progress, "delayed", Stage:Stage, "strategy") + COUNTIFS(Progress:Progress, "delayed", Stage:Stage, "tender") + COUNTIFS(Progress:Progress, "delayed", Stage:Stage, "evaluation") + COUNTIFS(Progress:Progress, "delayed", Stage:Stage, "contract")

What's odd is that I am getting the calculations right for my active and on-time projects even if I am using the similar formula.

=COUNTIFS(Progress:Progress, "on-time", Stage:Stage, "planning") + COUNTIFS(Progress:Progress, "on-time", Stage:Stage, "strategy") + COUNTIFS(Progress:Progress, "on-time", Stage:Stage, "tender") + COUNTIFS(Progress:Progress, "on-time", Stage:Stage, "evaluation") + COUNTIFS(Progress:Progress, "on-time", Stage:Stage, "contract")

Kind regards,

Grace

Grace

Hi Shaine,

I just wanted to check if there's any update regarding this please?

Thanks,

Grace

Andrew Stewart

Hi Grace,

Hopefully you have sorted this out by now, but if not....

As Shaine mentioned, you should remove the filters to see why you are not  getting the correct result. It appears you have a filter on the Status column that is set to "Active".

You also have a filter on the project name. I can't tell exactly what it is, but I suspect that it includes project names matching Strategy, Contract, etc.

There is probably a row with either

• a status that is not active, but a valid stage and progress delayed
• a typo in the project name column, so it is hidden by your filter, but which has a valid stage and progress delayed

If you set exactly the same criteria in the filters as you have in your countifs, you should find the culprit.

You could also simplify your criteria, how about just counting the Project header lines? One way to identify these is a non-blank reference, I think, which simplifies your formula to  

=COUNTIFS([Project Ref #]:[Project Ref #],<>"",Progress:Progress, "on-time", Status:Status, "active")

and 

=COUNTIFS([Project Ref #]:[Project Ref #],<>"",Progress:Progress, "delayed", Status:Status, "active")

since you do not care about the stage of the project, just that it is active.

Grace

Hi Andrew,

Thank you for your response. You were right about using the  Project Ref # column! It makes more sense now.

Cheers,

Grace

Andrew Stewart

Glad to have been able to help

Shaine Greenwood

Hi Grace—

Sorry for not seeing and responding back to this earlier. Looks like Andrew pitched in and worked out a solution with you. 

Thanks Andrew!