# Same Column - Row Type Header = Sum value of Row Type Detail

Options
✭✭

Based on the image below

There are two Row Type: (1) Header and (2) Detail.

JE REPORT is the grouping of these records.

AMT = free form data entry

Debit = if(AMT>0, AMT, 0)

Credit = if(AMT<0, AMT,0)

Question:

I want to sum Row Type = Header for all Detail rows of the same JE REPORT.

The first row would sum of RowNum 00074 - 00087, so the value of DEBIT would be \$9,502.00 and CREDIT would be = -\$9,502.00.

Is this possible?

Thank you

• ✭✭✭✭✭✭
Options

The easiest way to do this would be to remove the column formula, indent the detail rows, and then make the header column formulas read:

```=SUM(CHILDREN())
```

You should keep the same formula in the children rows. There are a few ways to manage this, so if this isn't a good fit you can look into other options.

Katy Hall

ILLA Canna

• ✭✭✭✭
Options

Debit column:

=IF([Row Type]@row = "Header", SUMIFS(AMT:AMT, [JE Report]:[JE Report], [JE Report]@row, [Row Type]:[Row Type], "Detail", AMT:AMT, >0), IF(AMT@row > 0, AMT@row, ""))

Credit column:

=IF([Row Type]@row = "Header", SUMIFS(AMT:AMT, [JE Report]:[JE Report], [JE Report]@row, [Row Type]:[Row Type], "Detail", AMT:AMT, <0), IF(AMT@row < 0, AMT@row, ""))

Rich @Prodactive

Rich Coles

Prodactive | Smartsheet-aligned Platinum partners

Check out our Smartsheet-dedicated YouTube channel for tips, tricks and inspiration

• ✭✭✭✭
Options

This part of the formula ensures it only counts items that are listed as 'Detail' ie excludes header, and then only counts values above zero in the debit column and below zero in the credit column.

Trust that makes sense - I'm assuming you're importing the data via Data Shuttle / API / pasting it in, hence you need the column formula.

Enjoy!

Rich @ Prodactive

Rich Coles

Prodactive | Smartsheet-aligned Platinum partners

Check out our Smartsheet-dedicated YouTube channel for tips, tricks and inspiration

• ✭✭✭✭✭✭
Options

The easiest way to do this would be to remove the column formula, indent the detail rows, and then make the header column formulas read:

```=SUM(CHILDREN())
```

You should keep the same formula in the children rows. There are a few ways to manage this, so if this isn't a good fit you can look into other options.

Katy Hall

ILLA Canna

• ✭✭✭✭
Options

Debit column:

=IF([Row Type]@row = "Header", SUMIFS(AMT:AMT, [JE Report]:[JE Report], [JE Report]@row, [Row Type]:[Row Type], "Detail", AMT:AMT, >0), IF(AMT@row > 0, AMT@row, ""))

Credit column:

=IF([Row Type]@row = "Header", SUMIFS(AMT:AMT, [JE Report]:[JE Report], [JE Report]@row, [Row Type]:[Row Type], "Detail", AMT:AMT, <0), IF(AMT@row < 0, AMT@row, ""))

Rich @Prodactive

Rich Coles

Prodactive | Smartsheet-aligned Platinum partners

Check out our Smartsheet-dedicated YouTube channel for tips, tricks and inspiration

• ✭✭
Options

=IF([Row Type]@row = "Header", SUMIFS(AMT:AMT, [JE Report]:[JE Report], [JE Report]@row, [Row Type]:[Row Type], "Detail", AMT:AMT, >0), IF(AMT@row > 0, AMT@row, ""))

What does this part of SUMIFS do --> [Row Type]:[Row Type], "Detail", AMT:AMT, >0

• ✭✭✭✭
Options

This part of the formula ensures it only counts items that are listed as 'Detail' ie excludes header, and then only counts values above zero in the debit column and below zero in the credit column.

Trust that makes sense - I'm assuming you're importing the data via Data Shuttle / API / pasting it in, hence you need the column formula.

Enjoy!

Rich @ Prodactive

Rich Coles

Prodactive | Smartsheet-aligned Platinum partners

Check out our Smartsheet-dedicated YouTube channel for tips, tricks and inspiration

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!