Total SUM Of One row for another Parent row but not children

ventureme

metrics smartsheet.png

I need to total SUM of Hour Required Row pulling from Parent (totaling children and not both Parent + Children as "Escalation Support" 11.00 already includes Assigned Cases and Open Investigations 5.00.

KPH

Hi @Jon Perry Melink

The data you have in your column, 1h 30m, is going to be read as text by smartsheet. It cannot do math on text so the report will return 0.

If you were to separate your hours and minutes into separate columns, you could then sum the hours and minutes.

You could separate them using a formula and retain the original column if you want/need.

How to extract minutes and hours from your text column

If you have the column set as a Duration format, change it to Text/Number as Duration is useful for durations in days, as soon as you put text in there instead it will get messy.

Then add some new columns.

I assume your duration column could have 1 or 2 digits before the "h" and 1 or 2 digits before the "m". So have written a couple of formulas to find the numbers we need.

New column 1 - extract hours from your cell

This formula looks at the string in your Duration column, takes the characters to the left of the "h" and converts them into a value (rather than text):

=VALUE(LEFT(Duration@row, FIND("h", Duration@row) - 1))

I would wrap this in an IFERROR function to return a blank if there are no hours (rather than an error message):

=IFERROR(VALUE(LEFT(Duration@row, FIND("h", Duration@row) - 1)), "")

Or better still produce a 0 when there is an error (before converting the string to a value) so you can do math on this:

=VALUE(IFERROR(LEFT(Duration@row, FIND("h", Duration@row) - 1), "0"))

New column 2 - extract minutes from your cell

This formula looks at the string in your Duration column, takes the characters between the "h " and the "m" and converts them into a value:

=VALUE(MID(Duration@row, FIND("h ", Duration@row) + 2, FIND("m", Duration@row) - (FIND("h ", Duration@row) + 2)))

As above, I would incorporate an IFERROR:

=VALUE(IFERROR((MID(Duration@row, FIND("h ", Duration@row) + 2, FIND("m", Duration@row) - (FIND("h ", Duration@row) + 2))), "0"))

(if you ever have minutes without hours at the start then we would need a different formula)

Make both formula column formulas.

Example

Your new columns will look something like this:

Using the new columns

You could then combine these into one column for the duration in hours or duration in minutes and use that in your report. You can do this by combining the formula (which means you can delete the columns above) but to get started I think it would be best to simply refer to the two columns you have.

If you wanted to show the duration in hours it would be

=Hours@row + (Minutes@row / 60)

Or in minutes, it would be

=(Hours@row * 60) + Minutes@row

Then you can create reports like this:

Or

You might want to use the ROUND function to make this cleaner.

Basically, once you get your durations into numeric values, you can do almost anything!

KPH

You can flag rows that are parent rows and only count those. HOWEVER, you also have rows in the dataset that are not parents and look like they should be counted. So I suggest you use the inverse logic and sum only rows that are not parents. This will give you the sum of the children and the rows without children.

You can add a helper column to the data to count the number of children a row has. Use this formula as a column formula (and hide the column so it doesn't bother you). In my example I'll call this column Count Child

=COUNT(CHILDREN())

Then use a SUMIF rather than a SUM to SUM the Hours Required only if the Count Child column is 0, like this:

=SUMIF([Count Child]:[Count Child], 0, [Hours Required]:[Hours Required])

Jon Perry Melink

Is there a way to create a report and total the amount of hours entered into a widget? I have a column setup for duration (hrs: 1h 30m, example). The row report does not calculate the sum, just says 0.

KPH

Hi @Jon Perry Melink

The data you have in your column, 1h 30m, is going to be read as text by smartsheet. It cannot do math on text so the report will return 0.

If you were to separate your hours and minutes into separate columns, you could then sum the hours and minutes.

You could separate them using a formula and retain the original column if you want/need.

How to extract minutes and hours from your text column

If you have the column set as a Duration format, change it to Text/Number as Duration is useful for durations in days, as soon as you put text in there instead it will get messy.

Then add some new columns.

I assume your duration column could have 1 or 2 digits before the "h" and 1 or 2 digits before the "m". So have written a couple of formulas to find the numbers we need.

New column 1 - extract hours from your cell

This formula looks at the string in your Duration column, takes the characters to the left of the "h" and converts them into a value (rather than text):

=VALUE(LEFT(Duration@row, FIND("h", Duration@row) - 1))

I would wrap this in an IFERROR function to return a blank if there are no hours (rather than an error message):

=IFERROR(VALUE(LEFT(Duration@row, FIND("h", Duration@row) - 1)), "")

Or better still produce a 0 when there is an error (before converting the string to a value) so you can do math on this:

=VALUE(IFERROR(LEFT(Duration@row, FIND("h", Duration@row) - 1), "0"))

New column 2 - extract minutes from your cell

This formula looks at the string in your Duration column, takes the characters between the "h " and the "m" and converts them into a value:

=VALUE(MID(Duration@row, FIND("h ", Duration@row) + 2, FIND("m", Duration@row) - (FIND("h ", Duration@row) + 2)))

As above, I would incorporate an IFERROR:

=VALUE(IFERROR((MID(Duration@row, FIND("h ", Duration@row) + 2, FIND("m", Duration@row) - (FIND("h ", Duration@row) + 2))), "0"))

(if you ever have minutes without hours at the start then we would need a different formula)

Make both formula column formulas.

Example

Your new columns will look something like this:

Using the new columns

You could then combine these into one column for the duration in hours or duration in minutes and use that in your report. You can do this by combining the formula (which means you can delete the columns above) but to get started I think it would be best to simply refer to the two columns you have.

If you wanted to show the duration in hours it would be

=Hours@row + (Minutes@row / 60)

Or in minutes, it would be

=(Hours@row * 60) + Minutes@row

Then you can create reports like this:

Or

You might want to use the ROUND function to make this cleaner.

Basically, once you get your durations into numeric values, you can do almost anything!

Jon Perry Melink

https://community.smartsheet.com/discussion/comment/414691#Comment_414691

Thank you so much, worked like a gem!!!

KPH

Great news @Jon Perry Melink

@System

Can we split this discussion? The accepted answer is to Jon's question 1/20. The answer to the original question is the one on 1/17. Thanks!