Help on formula: Countifs with multiple columns and criteria

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

Find more posts tagged with

Formulas

Solution Center

Comments

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:

Disable any filters on your sheet by clicking the filter button in the toolbar
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.
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:

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.

Kind regards,

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?