Row sum based on column value?

Andrew Buck

 

Here is a bit of an odd ball.  This is a budget sheet that has been simplified for this question.  At the end of each quarter the planed amount is replaced with the actual value for the quarter.  I need to sum up the total actual and the total planned across all quarters for use in other calculations.

 

Is there as way to sum across all columns but only include a quarter based on the value of the row that says actual or planned?  The goals would be for the user to come in at the end of the quarter and change the header for a quarter from planned to actual and enter the actual amount spent.  As it is now, the user has to edit each formula (shown) to add another actual and remove another planned.  It seems to me that there should be a smart(sheet)er way to do this, but I'm too much of a newbie to figure out how.  Thanks!

 

Peter Kirkham

Andrew, you just need to use the SUMIF function which has the syntax SUMIF(criteria_range, criteria_value, sum_range). Something like:

 

=SUMIF([Column3]2:[Column3]2, "Planned", [Column3]4:[Column8]4)

 

would work in your case (substitute the column names and row numbers of course).

 

More help on the function is http://help.smartsheet.com/customer/portal/articles/775363#advanced which can easily be accessed from the reference option in the formula button on the sidebar.

 

Peter