Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Sum of a Cell Range, Dependent on a Dropdown Value

BrentonJohn
BrentonJohn ✭
edited 12/09/19 in Archived 2015 Posts

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

 

 

 

 

Revenue Log.JPG

Tags:
· Share on FacebookShare on Twitter

Comments

  • Allison Kneubuhl
    Allison Kneubuhl ✭

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

    · Share on FacebookShare on Twitter
  • Travis
    Travis Employee

    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

    · Share on FacebookShare on Twitter
  • BrentonJohn
    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")

    · Share on FacebookShare on Twitter
  • Travis
    Travis Employee

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

    · Share on FacebookShare on Twitter
  • kapears76161
    kapears76161 ✭✭✭
    edited 10/06/19

    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?

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories