# Help on formula: countifs with multiple criteria

edited 12/09/19

Hi everyone,

I need help on the below formula. I am trying to get the number of active projects based on the columns Section and Stage (see attached image).

The 2 sections are called Food and Non-Food.

The active stages are called Planning, Strategy Decision, Tender, Evaluation and Contract. Other terms applied in this column are Awaiting Kick-off and Closed.

At the moment, there are no projects recorded for Food however I am getting a value. The expected value should be 0.

If anyone can advise the correct formula for this would be very much appreciated.

=COUNTIFS(Section:Section, "Food") + COUNTIFS(Stage:Stage, "Planning") + COUNTIFS(Stage:Stage, "Strategy Decision") + COUNTIFS(Stage:Stage, "Tender") + COUNTIFS(Stage:Stage, "Evaluation") + COUNTIFS(Stage:Stage, "Contract")

Grace

• ✭✭✭✭✭

Grace,

I am not totally clear on what you are asking here, but your formula will total the different stages plus the number of 'Food' sections. If you looking for the total stages for each section (Food vs. Non-Food), below is how I would do it.

At the bottom, I included the formula for the first two cells. Take note that I have used the =COUNTIFS formula, which allows you to count based on multiple conditions. You then would follow this pattern for the following rows. Let me know if this is not what you were trying to do here, and I can try to help out.

• Hi Steve,

Thank you for the response. I hope I could explain myself better.

I have a separate sheet for my summary info where I do my calculations.

Part of this summary is to get the number of Active, Future and Completed projects created by each sections. I already have a formula for the Stage column which looks like the below.

=IF(Status182 = "Active", [Project Name]182, IF(Status183 = "Active", [Project Name]183, IF(Status184 = "Active", [Project Name]184, IF(Status185 = "Active", [Project Name]185, IF(Status186 = "Active", [Project Name]186, IF(Status182 = "Not Started", "Awaiting Kick-off", IF(Status183 = "Not Started", [Project Name]183, IF(Status184 = "Not Started", [Project Name]184, IF(Status185 = "Not Started", [Project Name]185, IF(Status186 = "Not Started", [Project Name]186, IF(Status186 = "Completed", "Closed")))))))))))

Now what I need is to count the number Active projects using the parent rows. If I count using the "Active" status, it will count all the rows with active status that's why I tried using the formula below.

There are 40 projects created by Non-Food and 0 for Food. When I checked manually, there are 29 Active Projects for Non-Food however the result of the below formula is greater than that. I don't know where the values is coming from and I can't figure out where.

If I remove the (Section:Section, "Non-Food") part, I am getting the values right but with that, it becomes wrong.

=COUNTIFS(Section:Section, "Non-Food") + COUNTIFS(Stage:Stage, "Planning") + COUNTIFS(Stage:Stage, "Strategy Decision") + COUNTIFS(Stage:Stage, "Tender") + COUNTIFS(Stage:Stage, "Evaluation") + COUNTIFS(Stage:Stage, "Contract")

Kind regards,

Grace

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!