sumif n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
SUMIF() PowerPoint Presentation
Download Presentation
SUMIF()

Loading in 2 Seconds...

play fullscreen
1 / 8

SUMIF() - PowerPoint PPT Presentation


  • 109 Views
  • Uploaded on

SUMIF(). Lesson: BASIC. =SUMIF(). When Do You Use It: To add numbers based on a single criterion/condition Example: If you have a column of data with numbers and you only want to add those that are greater than a certain number. =SUMIF(range, criteria, [ sum_range ]). Required Arguments

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'SUMIF()' - adelio


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
sumif

SUMIF()

Lesson: BASIC

sumif1
=SUMIF()
  • When Do You Use It:
    • To add numbers based on a single criterion/condition
  • Example: If you have a column of data with numbers and you only want to add those that are greater than a certain number.
sumif range criteria sum range
=SUMIF(range, criteria, [sum_range])
  • Required Arguments
    • range: The range of cells that you want evaluated by criteria. Note that any blank cells in the range will be ignored
    • criteria: This is the condition that defines which cells will be added. It can be expressed as a number, expression, a cell reference, text or a function
    • sum_range: The actual cells you want to add. If it is the same range as the ‘range’ above, you don’t need to include it [rarely is that the case], so this argument is considered optional.
sumif example 1
=SUMIF() : Example 1

This is how the formula looks if you simply type in the criteria you want. Note that text criteria need to be enclosed in double quotation marks.

This is how the formula looks if your criteria is a reference to a value in a cell.

Which method is better? I prefer the cell reference method for at least two reasons. First, if labeled, you can see what the criteria is without having to go into the formula. Second, I find it easier if I have to change the criteria if I just have to type over the value in the cell reference than selecting the cell with the formula, getting it in edit mode, and then typing over in just the right spot in the formula.

sumif example 2
=SUMIF() : Example 2

This is how the formula looks if you use an expression, like >, <, =, <=, etc. Note that expression criteria need to be enclosed in double quotation marks.

This is how the formula looks if your criteria is a number. It’s a lot like the text criteria except you don’t need the double quotes.

Which method is better? I still prefer the cell reference method from the last example because you can still achieve the results of both these examples. To mimic the expression criteria, just enter >25 in the cell being referenced: =SUMIF(A2:A13,D13,B2:B13) where in D13 you’ve typed >25.

sumif example 3
=SUMIF() : Example 3

This is how the formula looks if you use a formula as a criteria. Other formulas you might find yourself using are VLOOKUP(), HLOOKUP() and INDIRECT().

I don’t come across this too often, but it is available. On a more technical note, any one of the arguments could be a formula, so long as the result of the formula returns the expected data type, such as a range for the first and third arguments.

sumif limitations
=SUMIF() : Limitations
  • The biggest limitation to the SUMIF() function is that you can only enter in a single criteria.
  • How to get around this limitation:
    • If you have Excel 2007, you can use the SUMIFS() formula
    • Use the SUMPRODUCT() formula
    • Create a column of concatenated values of the other columns and then have your range reference that new column and your criteria be the concatenation of your multiple criteria
sumif alternatives
=SUMIF() : Alternatives
  • There are other ways to accomplish what the SUMIF() function does. I’ll list some here, but won’t go into detail.
    • nest the SUM() function inside the IF() function (Array Function)
    • Use the SUMPRODUCT() function
    • Use the DSUM() function
    • use the Conditional Sum Wizard (requires installing the Conditional Sum Wizard Add-In)