Help with formulas to have data in the Summary Sheet

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!

Jen Lange

Hi @Pooja Shengali , if I understand your use case, you're looking to add some formulas into your sheet summary to pull into your Dashboard. Is this accurate? If so, I think the following COUNTIFS formula should work well for you.

=COUNTIFS(Country:Country, "USA", [SmartERP Owners]:[SmartERP Owners], <>"")

Let me know if this helps. Thanks!

-Jen

Jen Lange

That's great, @Pooja Shengali!

Also, thank you for your engagement and contribution to our community. Your inquiry is likely to assist other members across time AND it helps me flex my skills.

If you appreciate my response, please recognize the effort by with an "Insightful" or "Vote Up" selection. Thanks.

-Jen

Jen Lange

Hi @Pooja Shengali , if I understand your use case, you're looking to add some formulas into your sheet summary to pull into your Dashboard. Is this accurate? If so, I think the following COUNTIFS formula should work well for you.

=COUNTIFS(Country:Country, "USA", [SmartERP Owners]:[SmartERP Owners], <>"")

Let me know if this helps. Thanks!

-Jen

Pooja Shengali

Thanks a ton, Jen! You made my life easy :) This was indeed helpful.

Jen Lange

That's great, @Pooja Shengali!

Also, thank you for your engagement and contribution to our community. Your inquiry is likely to assist other members across time AND it helps me flex my skills.

If you appreciate my response, please recognize the effort by with an "Insightful" or "Vote Up" selection. Thanks.

-Jen

Pooja Shengali

One last help please..

=COUNTIFS(Country:Country, "USA", [SmartERP Owners]:[SmartERP Owners], <>"")

The above formula gives "All Statuses" count however I need ONLY "In Progress" & "Not Started" in the Summary Sheet for the Upcoming Tasks for SmartERP | USA..

Jen Lange

Try this, @Pooja Shengali.

=COUNTIFS(Country:Country, "USA", [SmartERP Owners]:[SmartERP Owners], <>"", Status:Status, "Not Started") + COUNTIFS(Country:Country, "USA", [SmartERP Owners]:[SmartERP Owners], <>"", Status:Status, "In Progress")

Or

=COUNTIFS(Country:Country, "USA", [SmartERP Owners]:[SmartERP Owners], <>"", Status:Status, <>"Complete")