Build a COUNTIFS formula

System

This discussion was created from comments split from: #UNPARSEABLE error.

Sunny Kaul

Hi,

I am trying to build the COUNTIFS formula to get a combination of Country+Milestone Phase+Status. Ex: Result should be the count 10 for "USA+1- Planning+Complete"

Please help me correct the below formula with the below-mentioned columns.

=COUNTIFS(Country:Country,"USA"),(Milestone Phase:Milestone Phase,"1- Planning"),(Status:Status,"Complete")

Column1 - Country

USA

CAN

UK

Column2 - Milestone Phase

1- Planning

2- Discovery & Design (includes CRP)

3- Build and Track UAT

4- Big-Bang UAT (Testing)

5- Soft Go-Live (HR)

6- Parallel Runs

7- Go-Live

8- Post Go-Live Support

Column3 - Milestone Status

Not Started

In Progress

Complete

Thank you!

Paul Newcome

@Sunny Kaul Try this...

=COUNTIFS(Country:Country, "USA", [Milestone Phase]:[Milestone Phase], "1- Planning", Status:Status, "Complete")

Sunny Kaul

Thank you Paul!

I tried the above formula, but the result is "0" which is incorrect. Also tried with other statuses too but the result is "0". Need your help on this.

Paul Newcome

Make sure all spelling is correct to include spaces in both the formula and the data.

If you enter this into a cell:

1-space space space Planning

It will only show as

1-space Planning

on the sheet, but you won't get a match because those extra spaces are still stored.

Sunny Kaul

Thanks a ton, Paul! It worked fine with expected results.

Paul Newcome

Great. Happy to help. 👍️

Sunny Kaul

Hey Paul,

I have created the below individual reports for the upcoming tasks in the Smartsheet. This has a combination of Tasks + Country + Owners + Status.

However, I also need this data in the Dashboard.

Could you please help me with formulas to have this data in the Summary Sheet. So that I can pull the data from the summary sheet to create "Metric" or "Chart" widgets in my Dashboards.

1. Upcoming Tasks for Crawford | USA
2. Upcoming Tasks for Crawford | CAN
3. Upcoming Tasks for Crawford | UK
4. Upcoming Tasks for Crawford | GBSC

1. Upcoming Tasks for SmartERP | USA
2. Upcoming Tasks for SmartERP | CAN
3. Upcoming Tasks for SmartERP | UK
4. Upcoming Tasks for SmartERP | GBSC

See below the columns in use:

Your help with this will be greatly appreciated.

Thanks a ton!

Pooja Shengali

Hey Paul,

I have created the below individual reports for the upcoming tasks in the Smartsheet. This has a combination of Tasks + Country + Owners + Status.

However, I also need this data in the Dashboard.

Could you please help me with formulas to have this data in the Summary Sheet. So that I can pull the data from the summary sheet to create "Metric" or "Chart" widgets in my Dashboards.

1. Upcoming Tasks for Crawford | USA
2. Upcoming Tasks for Crawford | CAN
3. Upcoming Tasks for Crawford | UK
4. Upcoming Tasks for Crawford | GBSC

1. Upcoming Tasks for SmartERP | USA
2. Upcoming Tasks for SmartERP | CAN
3. Upcoming Tasks for SmartERP | UK
4. Upcoming Tasks for SmartERP | GBSC

See below the columns in use:

Your help with this will be greatly appreciated.

Thanks a ton!