SUMIFS With multiple criteria

Travis Myers

I am having problems wrapping my head around this particular formula.

=SUMIF({Follow Up Contractors}, Contractor@row, {Follow Up Lighting}) is what I have so far, which works but it isn't drilled down like I need it to be.

Logically, I am after SUM (range 1) IF (range 2) matches Contractor@row AND is within date range 1/1/21-1/31/21 based on date column within Follow Up.

EDIT:

I would also like to add that there is a slight variation in how the Contractor@row can appear. I handled this in Excel with a wildcard, but I don't believe that is a thing in Smartsheet's.

Travis Myers

I have come across another predicament with this. The number I am trying to sum doesn't always exist on the same row as the contractor name. The value I am after can be the parent row and the contractor name could be in an indented child row.

Is it even possible to specify if the name is contained within a child row to sum the value in its parent row?

Jaykel Torres

Hey @Travis Myers,

When you want to Sum data that meet multiple criteria, you will want to use the SUMIFS Function opposed to SUMIF (which allows a single criteria to be evaluated). You will also want to include the DATE Function in your formula when evaluating Date Values. Consider using the CONTAINS Function in the case you are trying to find certain text within a cell that does not have to be an exact match.

While it is possible to evaluate only parents or children rows separately, you may need to create an additional Formula (most likely in a separate column) that can indicate which Rows should be evaluated; this does not have to be a Formula but can simply be a checkbox with specific Rows checked. The Column should be included within your SUMIFS Function as a range/criteria to be evaluated.

I hope this helps!

Jaykel