Hello Excellers – #formulafriday again, today let’s look at calculating the total sales of all but one item in data list, we can do this with the SUMIF function and instruct Excel to omit the values of the item we specifiy.

First let’s take a look at the syntax of SUMIF.

=SUMIF(range, criteria, sum_range)

Where

range – this is required and  is the data range you want Excel to evaluate

criteria  – this is required and needs to be in the form of a number, expression, a cell reference, text, or a function which defines which cells will be summed. NB any text criteria or criteria that includes any mathermatical or logical symbols MUST be enclosed in double quotation marks (“).  For Example 31, “<31” ,A32, “Apples” etc.

sum_range – this is optional, and is the actual cells thtat you want Excel to add other than those specified in the range; if this argument is omitted then Excel will use the cells in the range argument – the same range of cells to which the criteria is applied

Ok let’s take a look at an example and calculate the total sales of all of our products except for “Green Paper”.  Here is my data set :-

 

I want to see all of my sales of paper but not include or excluding the Green Paper product.  The formula looks like this-

 

 

I have enclosed the criteria of <>(not like) Green Paper in double quotation marks as it is a logical as well as a text condition. Excel has summed all of the Paper Product Sales except Green Paper.  If we did not use the “” marks Excel we get a warning from Excel so we know we have to use the inverted commas.

 

If you want to download Excel file to play around with the example used in the blog post you can do so below.

[wpdm_package id=’10024′]