Best Of

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

@Darren Mullen Thank you this formula works. Figured out that one of my criteria was typed incorrectly. Fixed that and viola

=COUNTIFS([Client Final Approval]:[Client Final Approval], "Needs Review", [Fit/Gap]:[Fit/Gap], OR(@cell = "Gap – In Scope", @cell = "Fit – In Scope"))

It worked! Thank you so much. Really appreciate your time and help.

Hi Lisa,

Try the below, personally I think scenario 1 below is cleaner and easier to understand for a viewer other than yourself.

Scenario 1 - Total Estimated Costs:

=IFERROR(IF(Hours@row <> "", Hours@row * [Hourly Cost]@row, IF(Units@row <> "", Units@row * [Unit Cost]@row, [Fixed Cost]@row)) + IF(Hours@row <> "", Hours@row * [Hourly Cost]@row, IF(Units@row <> "", Units@row * [Unit Cost]@row, [Fixed Cost]@row)) * [% Markup]@row, "")

Scenario 2 - % Markup applied to each cost type:

Hourly Cost:

=IF(Included@row = false, "", INDEX({Hourly Cost}, MATCH([Project ID | Project | Category]@row, {Category}, 0))) + IF(Included@row = false, "", INDEX({Hourly Cost}, MATCH([Project ID | Project | Category]@row, {Category}, 0))) * [% Markup]@row

Unit Cost:

=IF(Included@row = false, "", INDEX({Unit Cost}, MATCH([Project ID | Project | Category]@row, {Category}, 0))) + IF(Included@row = false, "", INDEX({Unit Cost}, MATCH([Project ID | Project | Category]@row, {Category}, 0))) * [% Markup]@row

Fixed Cost:

=IF(Included@row = false, "", INDEX({Fixed Cost}, MATCH([Project ID | Project | Category]@row, {Category}, 0))) + IF(Included@row = false, "", INDEX({Fixed Cost}, MATCH([Project ID | Project | Category]@row, {Category}, 0))) * [% Markup]@row

Many thanks,

Adam

Hi,

I hope you're well and safe!

Try something like this. (Replace the column names to match yours)

=IF(COUNTIFS([Column 001]@row:[Column 002]@row, "Attended") > 0, 1)

Did that work/help?

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Smartsheet Expert Consultant & Partner / CEO @ WORK BOLD

✅ Did I help answer your question/solve the problem? Please support with💡 ⬆️ ❤️, and/or ✅ Answer. This will make it easier for others to find a solution or help answer! I appreciate it, thank you! 🙏

@Tomas Giele , this would be very easy using the APIs. Basically just listen for the checkbox being checked. Then read through the results as in the example. Then just use a key-value pair for each candidate updater and update the respective field for the row.

Sample data from API docs.

 {
            "columnId":4567890123456789,
            "displayValue": "Revision 3",
            "columnType": "TEXT_NUMBER",
            "value": "Revision 3",
            "modifiedAt": "2013-06-24T00:10:18Z",
            "modifiedBy" : {
                "name" : "Jane Smart",
                "email" : "jane.smart@smartsheet.com"
            }
        },