↑ Return to Condition

SUMIF function

This function SUMIF works like the COUNTIF function but here, you don’t count the number of cells within a criteria, but you add the cells of this criteria.

The SUMIF Function

The SUMIF function needs 3 parameters.

  • The range of criteria
  • The criteria
  • The range to add

Example

For instance, you want to count the number of pen sell in the month.

In H4, write the beginning of the formula and select the range of cell where you have your product.

=SUMIF(B3:B12;

Then, you write your criteria or the reference where you have your criteria. At this point, there is no difference with the COUNTIF function.

=SUMIF(B3:B12;”Pen”;

or

=SUMIF(B3:B12;G4;

You finish with the range of cell where you have your data to add. So, if you want to return the number of pen sold, you will select the range C3:C12.


The result is 245 ; 75 (row 3) + 50 (row 6) + 100 (row 8 ) + 20 (row 12)

=SUMIF(B3:B12;G4;C3:C12)

If you want to return the amount of sales for the pen, you select here, in this case the column E as 3rd parameter ; the result is 367,5.

=SUMIF(B3:B12;G4;E3:E12)

Fill the following document with the function SUMIF. In the row 4, you have an example (double-click on cells to edit it).

Example with Greater Than

Now, as the COUNTIF function, you can have, as a criteria, a condition (greater than or less than).

For instance, you want to know the amount of sales before or after the 10/08/2011. For the first criteria, you will select the range of cell with the dates because our criteria is a date.

The second criteria can be written in two ways

  • first: “> = 10/08/2011”
  • or if you want to use the value in cell “>” & G4

The symbol & is compulsory to link the operator, between quotes, and the cell reference.

For the last parameter, you select the range of cell ‘Totals’ because that’s what you want to add.

Your formula is:

=SUMIF($A$3:$A$12,”>=”$G4,$E$3:$E$12)

In the following workbook, you have an example with the operator “less than”





4 comments

Skip to comment form

  1. Monica Galindo

    Your lips are moving! I know you lie! 😛

  2. Monica Galindo

    hola soy lota la pelota

  3. juana de arco

    yes,yes,yes,yes, I finally did it

  4. Petros

    Do you want to read about the syntax and usage of an Excel 2013 or VBA function in your native language and practice with a demo workbook ?

    The facts:
    There are over 600 Excel & VBA functions in Office 2013.
    Excel functions have been translated in 16 languages.
    Microsoft offers over 20,000+ function help webpages in 50+ languages.

    How to navigate fast among so many help pages ?

    This free Ribbon Add-in will help you navigate to Microsoft’s online help pages with embedded workbooks, which can be downloaded to your computer for function inspection and in-depth practice.

    http://www.spreadsheet1.com/excel-2013-translated-functions-free-addins.html

Leave a Reply

%d bloggers like this: