Microsoft Excel Tips and Tricks - Computergaga

Home / Archives for Formulas

Sum Negative Values Only in Excel

You may have scenario on a spreadsheet when you want to sum negative values only in a range.

Take the example below for instance, it contains a list of incoming and outgoing payments. If we needed the total for outgoings, we would need to sum the negative values only.

Sum negative values only in Excel to sum outgoing payments

This can be done using the SUMIF function. This function allows us to sum only the values that meet a specific condition.

The formula below will total the outgoings in the list by summing only the values less than 0.

=SUMIF(B2:B12,"<0")

The criteria has been entered as text in double quotes. When entering criteria like this into the formula it needs to be entered as text.

The criteria specifies that only values less than 0 should be summed.

Watch the video

Advanced Techniques for Summing Values

Using the SUM function to total a column of values is an essential skill in Excel. However, it is not always that straightforward. The column of values may contain errors, or the values you want to sum may be on many different sheets.

This article looks at 4 advanced techniques for summing values including;

  1. Summing a range that contains errors
  2. Creating a cumulative total
  3. A consolidated sum from multiple sheets
  4. Summing Every Nth Value in a Range
[Read more…] about Advanced Techniques for Summing Values

Separate Text into Different Cells

A common requirement in Excel is the need to separate text into different cells. The reasons for this are numerous, but typically it is because the way the data was imported or received is not sufficient for your analytical needs.

This tutorial looks at four techniques for separating text.

  • Using Text to Columns to separate text
  • Extract name from an email address
  • Extract text between two characters
  • Separating a number from text
[Read more…] about Separate Text into Different Cells

Count Unique Values in Excel

A common question from Excel analysts and enthusiasts on my courses is to count unique values in a list.

This post looks at using a formula to calculate this distinct count.

Consider the list below of a list of delegates attending our courses. A normal count on this range will tell us how many attendances there were. That’s good, but we want to know how many unique attendees there were.

List of names including duplicates
[Read more…] about Count Unique Values in Excel

Sum Every Nth Row in a List

At some point you may need to sum the value from every nth row in a large list. Excel does not provide a function for this. Excel has a few different Sum functions, but not one to sum the value from every other, or every third, fourth, or fifth row in a list.

The spreadsheet below contains totals in every fifth row starting from row 3. We want to only add these sales totals.

List with sales totals to sum in every nth row
[Read more…] about Sum Every Nth Row in a List