Excel: SUM If Less Than

Formula to compute for values if cells have values less than a specified amount

Formula

=SUMIF(range, expression)

Add the values in cells if the value is greater than 3000

=SUMIF(A1:A8,"<3000")

Add the values in cells if the value is greater than 1000

=SUMIF(A1:A8,"<1000")

Add the values in cells if the value is greater than 500

=SUMIF(A1:A8,"<500")

Formula Parameters

  • Range – The first part of the formula lets you specify the range to add. This is specified like this, A1:A20
  • Expression – The second part of the formula lets you specify the condition. The condition is in string form. You can use multiple logical operators like <, =, <=, >=
=SUMIF(A1:A8,"<=2000")
=SUMIF(A1:A8,">=2000")
=SUMIF(A1:A8,"=2000")

Screen shot 2016 10 03 at 8.17.06 am

 Excel: SUM If Greater Than

Formula to compute for values if cells have values greater than a specified amount

Formula

=SUMIF(range, expression)

Add the values in cells if the value is greater than 4000

=SUMIF(A1:A8,">4000")

Add the values in cells if the value is greater than 2000

=SUMIF(A1:A8,">2000")

Add the values in cells if the value is greater than 1000

=SUMIF(A1:A8,">1000")

Formula Parameters

  • Range – The first part of the formula lets you specify the range to add.
  • Expression – The second part of the formula lets you specify the condition. The condition is in string form. You can use multiple logical operators.
=SUMIF(A1:A8,">=1000")
=SUMIF(A1:A8,"<=1000")

Screen shot 2016 10 03 at 8.11.00 am

 SUM of multiple columns with Rails ActiveRecord

When using ActiveRecord, we may think that you are too far from the real thing. It is actually very at implementing actual SQL statements.

What do you to get the SUM of multiple columns using ActiveRecord.

SELECT SUM(qhse+qhse_manager+managing_director+other_internal) FROM "stops"

This is very easy to implement with Rails ActiveRecord

ActiveRecord

@location_ytd_qhse = Stop.sum("qhse+qhse_manager+managing_director+other_internal")

This will produce our nice SQL Statement:

SQL Statement

SELECT SUM(qhse+qhse_manager+managing_director+other_internal) FROM "stops"

If you want to try something else, you can multiply column sum values:

ActiveRecord

@location_ytd_qhse = Stop.sum("qhse*qhse_manager*managing_director*other_internal")

SQL Statement

SELECT SUM(qhse*qhse_manager*managing_director*other_internal) FROM "stops"