Is there a way to use SumIF to add values in a column that match a specific text string in adjacent

Burt L
Burt L
edited 12/09/19 in Formulas and Functions

We are building estimate sheets using a hierarchy based on Construction Specifications Institute's organizational coding system.  I have rows nested using multiple parent / children.  The top level is the CSI Division, which has children rows with specific categories within that Division, and each Category line has nested rows with more specific rows breaking that item down into more granular detail.  I'm using conditional formatting and =sum(children) functions to roll up totals up the hierarchy.  This works well as it's easy to add rows at any level of the hierarchy and easy to set up filters to see the sheet with rows visible by level of parent / child hierarchy.

I want to pull values from a "Quantity" column where the cell contains a number (or a formula that produces a number).  A cell in the adjacent column "Unit" on the row contains a text string such as "Shr" for superintendent hour or "Chr" for carpenter hour.  The quantity cell is a vlookup formula that pulls a billable hourly rate from another sheet.

I cannot figure out how to create a formula that will sum the total hours in the Quantity column that match a specific text entry in the "Unit" column.  




Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!