It is common in Excel to use averages to summarise large data sets. It is also common to compare the averages across different segments. Here’s a technique you might find useful when comparing a segment against all other segments.

In the image below you can see the customer category is broken down into five segments. We’ve calculated the average selling price for each segment and the total.

Columns B and C have SUMIFS functions. Column D divides the amount by the quantity.

You could compare any segment to the average for the whole data set (8.80). But that total average includes the segment that you’re looking at.

The data is coming from the table below, which is named tblSales.

It might be useful to compare one segment against all other segments in the data set.

This is not hard to do using the SUMIFS function.

In the image below I have added another section to the bottom which compares retail against non-retail segments. As you can see the totals still add up.

The formula in cell B9 which extracts the quantity for retail is shown below.

=SUMIFS(tblSales[Quantity],tblSales[Customer Category],A9)

This is a standard SUMIFS function where the quantity is being added up when the customer category equals retail which is in cell A9.

This formula can be copied down to cell B10.

This works because the entry in cell A10 is.

<>Retail

When you use the less than symbol followed by the greater than symbol it means not equal to.

The entry in cell A10 means not equal to retail.

This means the SUMIFS function adds up all the entries in the quantity column when the customer category column is not equal to retail.

Cell C9 uses the same type of SUMIFS function, except it is adding up the amount column.

Having the less than and greater than symbols in the cell as in cell A10, is easier that inserting them in the function.

As an example, the following formula also calculates the non-retail quantity.

=SUMIFS(tblSales[Quantity],tblSales[Customer Category],"<>"&A9)

When you use the less than and greater than symbols together you need to enclose them in quotation marks. You then use the & symbol to join them to the value you are working with.