Sum of a Cell Range, Dependent on a Dropdown Value

BrentonJohn

Is there a way to add up the total of a cell, dependent on the drop down value of the row?  

 

For instance...  

 

I would like to total the sum of the revnue (shown in the "Live Revenue Log") by the day (shown in the "Daily Revenue Overview")

 

 

 

 

Allison Kneubuhl

Good one. I'd like to know as well!

Travis

Brenton - good question! This can be done with a SUMIF formula. You will sum the number IF the corresponding cell is Monday, Tuesday, etc.

 

Heres an example of the formula you can use. The cell references will need to be updated to match the cell ranges in your sheet.

 

=SUMIF(Day1:Day10, "Monday", Amount1:Amount10)

 

This formula will sum all the values in the Amount column, row 1-10 if the corresponding Day cell is “Monday”.

 

 

Here is a link to our Help Center article on formulas which can give you more information on formulas in Smartsheet: http://help.smartsheet.com/customer/portal/articles/775363-using-formulas

BrentonJohn

Works great! 

 

Since we are on the subject, lets take it a step further...

 

Can a value be summed based on multiple criterias of columns (I believe that this function is "SUMIFS")

Travis

The syntax for SUMIFS is similar to SUMIF, just note the sum criteria is now at the beginning of the formula.

 

Here is the syntax for SUMIFS: SUMIFS(sum_range, criteria_range1, criteria_value1, criteria_range2, criteria_value2...)

 

If you wanted to use your example above but only want to sum if the day is Monday and month is January, try this formula:

 

=SUMIFS(Amount1:Amount10, Day1:Day10, "Monday", Month1:Month10, "January")

kapears76161

I'm trying to do the same thing with the SUMIF formula, but do not want to specify the exact text "Monday" as shown above. Instead, I need to reference a cell within the row as criteria.

Example - I want to sum all donation values from an employee where their name is listed within the EEName column. I've been able to make this work when referencing other sheets, but I'm having trouble referencing @row and within same page columns.

Here's the formula I think should work: =SUMIF(EEName:EEName, EEName@row, $Value:$Value, 1)

What am i doing wrong?