Formula help to calculate sum of items across smartsheets

Carmen H

Hi Smartsheets community,

I've recently created a Smartsheet with all the different types of meetings and then I have separate columns for the count of meetings attended, missed and chaired.

In a separate worksheet, I wanted to create a summary (not using summary report) but it's only counting the number of rows created for the meetings (items) instead of calculating a total sum of the numbers in each row.

Worksheet 1 with raw data

Worksheet 2 with the summary

I want to be able to not only count the number of item name but get a total of all the meetings broken down by chaired, attended, missed? What is the formula and do i need to create additional columns? And what will the additional column formulas need to be? Noting that only the governance meetings have chaired, attended and missed. All other meetings are just the titles and then the number attended or chaired?

And because I've broken it down by half yearly, how would i be able to continue to show H1 and H2 and total? And in the future build upon it year on year?

thank you!

AdamSYNH

Hi @Carmen H,

Formulas for Totals:

for each label, substitute {Worksheet 1 - Meetings Chaired} cross-sheet reference for {Worksheet 1 - Attended as a member} etc. as needed for each row. To reference H2 2025 swap out "H1 2025" for "H2 2025" etc. as needed across multiple columns.

=SUMIFS({Worksheet 1 - Meetings Chaired}, {Worksheet 1 - Time Period}, "H1 2025", {Worksheet 1 - Scorecard Category}, "Governance")

You asked about adding additional columns, if you intend to report or visualize this data you are better off with a column for each time period. However, if you want to summarize the year within one column you can create a visually appealing summary with this trick:

1. Add a helper column with a cell that has "-[carriage return]-" (see highlighted column, on your keyboard you'd type - then Alt+Enter then -
2. Combine two of the formulas mentioned above, along with a SUBSTITUTE formula for the carriage return. Here's the formula as an example for 2025 Combined row 2

="H1 2025: " + SUMIFS({Worksheet 1 - Meetings Chaired}, {Worksheet 1 - Time Period}, "H1 2025", {Worksheet 1 - Scorecard Category}, "Governance") + SUBSTITUTE($[Helper Column for carriage return]$1, "-", "") + "H2 2025: " + SUMIFS({Worksheet 1 - Meetings Chaired}, {Worksheet 1 - Time Period}, "H2 2025", {Worksheet 1 - Scorecard Category}, "Governance")

Hope this all helps, do let me know if I've missed the ask, I'd be happy to take another look.

Adam

kowal

hi @Carmen H,

have you considered using the formula countif instead of sumif?

if you want to calculate the amount of rows that meets some criteria then countif will do the job .

Carmen H

hi @kowal thank you for your response, however I'm not trying to count the number of rows but rather get a total of the numbers of the total chaired or missed in the corresponding row. I'm not sure how to create that formula.

Thank you

AdamSYNH

Hi @Carmen H,

Formulas for Totals:

for each label, substitute {Worksheet 1 - Meetings Chaired} cross-sheet reference for {Worksheet 1 - Attended as a member} etc. as needed for each row. To reference H2 2025 swap out "H1 2025" for "H2 2025" etc. as needed across multiple columns.

=SUMIFS({Worksheet 1 - Meetings Chaired}, {Worksheet 1 - Time Period}, "H1 2025", {Worksheet 1 - Scorecard Category}, "Governance")

You asked about adding additional columns, if you intend to report or visualize this data you are better off with a column for each time period. However, if you want to summarize the year within one column you can create a visually appealing summary with this trick:

1. Add a helper column with a cell that has "-[carriage return]-" (see highlighted column, on your keyboard you'd type - then Alt+Enter then -
2. Combine two of the formulas mentioned above, along with a SUBSTITUTE formula for the carriage return. Here's the formula as an example for 2025 Combined row 2

="H1 2025: " + SUMIFS({Worksheet 1 - Meetings Chaired}, {Worksheet 1 - Time Period}, "H1 2025", {Worksheet 1 - Scorecard Category}, "Governance") + SUBSTITUTE($[Helper Column for carriage return]$1, "-", "") + "H2 2025: " + SUMIFS({Worksheet 1 - Meetings Chaired}, {Worksheet 1 - Time Period}, "H2 2025", {Worksheet 1 - Scorecard Category}, "Governance")

Hope this all helps, do let me know if I've missed the ask, I'd be happy to take another look.

Adam