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

mrmik

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

Katy H

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())

https://help.smartsheet.com/articles/504734-hierarchy-indenting-outdenting-rows?_gl=1*81qajg*_ga*MTcyODEyMjU1Ni4xNjQ0Mjc1NjE1*_ga_ZYH7XNXMZK*MTY2NDM5NjQ2Ni4xNC4xLjE2NjQ0MDAxNTIuNDMuMC4w&_ga=2.225751578.1414487602.1664381940-1728122556.1644275615

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.

Rich Coles

If you want to keep the column formulas (I always try to), then how about this...

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

Glad you liked it!

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

Katy H

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())

https://help.smartsheet.com/articles/504734-hierarchy-indenting-outdenting-rows?_gl=1*81qajg*_ga*MTcyODEyMjU1Ni4xNjQ0Mjc1NjE1*_ga_ZYH7XNXMZK*MTY2NDM5NjQ2Ni4xNC4xLjE2NjQ0MDAxNTIuNDMuMC4w&_ga=2.225751578.1414487602.1664381940-1728122556.1644275615

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.

Rich Coles

If you want to keep the column formulas (I always try to), then how about this...

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

mrmik

@Rich Coles

=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

Rich Coles

Glad you liked it!

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