[go: up one dir, main page]
Scribd
Upload
122 views4 pages

Sumif

The document discusses the SUMIF function in Excel which allows summing numbers from multiple cells based on criteria. It provides examples of using SUMIF to sum values where a column contains a specific text or where a date is greater than a given date. It stresses the importance of checking formulas and offers training resources to learn more about Excel functions.

Uploaded by

Che Mashepa
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
122 views4 pages

Sumif

The document discusses the SUMIF function in Excel which allows summing numbers from multiple cells based on criteria. It provides examples of using SUMIF to sum values where a column contains a specific text or where a date is greater than a given date. It stresses the importance of checking formulas and offers training resources to learn more about Excel functions.

Uploaded by

Che Mashepa
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 4

SUMIF()

As a business consultant who works heavily with Excel, I find many people do not know
how to use some of the useful functions in Excel (there are also some less useful ones).
The SUMIF() is very useful and rarely used by Excel users.

Like any „IF‟ statement SUMIF first has a criteria that must be met (the IF part). If the
value in the cell included in the range matches the criteria, (the if is True) then it adds the
corresponding number in the sum_range to its cumulative total for that criteria.

Just a short review of IF statements:

A simple „IF‟ statement says if a condition is true (in this example B2=$B$1) then do
something. This statement puts the value that is in C2 in D2 (D2 is where the formula is).
If this statement is not true then put a „0‟ in D2. The dollar sign makes the reference
absolute. You need to understand absolute vs. relative addresses to use Excel functions
effectively.

The SUMIF() does essentially the same thing only it can sum numbers from multiple
cells with one equation.

SUMIF(range,criteria,sum_range)

Range = the range to look in to find the Criteria


Criteria = what to look for in the Range
Sum_Range = what numbers to sum that match the items found in the Range from the
criteria
In the example above, we are asking Excel if the contents of any cell in the range A3:A10
is equal to the number in E3 add it to the total.

In this case, the cells in A4 and A8 contain „DOWN‟, which is the criterion in E3. The
corresponding numbers in B4 and B8 are 20 and 20 and are equal to 40 (the number in
E5).

In the SUMIF() examples below, I first determine the total dollar amount of all items
with a Y in the column.

=SUMIF(C2:C17,”Y”,B2:B17) = $3,382,585

This equation says if there is a “Y” in the cells between C2 and C17 then add the number
in the corresponding cell in column B to the running Total.

=SUMIF(C2:C17,"<>Y",B2:B17) = $5,813,311

In the second formula I am askig for all of the cells between C2 and C17 that do not
contain a „Y‟.

Notice that in the SUMIF() function that the criteria in the first formula is surrounded by
quotes ”Y”. In the second the “<>Y” is surrounded by quotes.
The third statement we refer to another cell and if the date in A2 through A17 is greater
than 8/1/2003 then add the corresponding number to the running total.

=SUMIF(A2:A17,”>”&E7,B2:B17) = $3,3659,961

In the third formula, I have referred to a cell location for my criteria. This makes for a
more dynamic formula. If you have not used the concatenate symbol before you might
want to look at my Playbook on “Including live numbers in text”.

Since we are referring to a cell, we need to combine or concatenate the operator with the
value in Cell E7. This is done by “>”&E7. This tells Excel to look for criteria that is
greater than the value in cell E7.

If you have not used the concatenate operator “&” before you might want to look at my
Playbook on “Including live numbers in text”. Also, see the Playbook on COUNTIF() a
cousin of the SUMIF() function.

Whenever developing an Excel worksheet it is vary important to double check the work.
Even a function like SUMIF() can give the wrong answer if you feed it incorrect
parameters.

The formulas in B18 and D18 are to check that what I did with the SUMIF()‟S in cells
D5 and D92 total the same as the standard SUM(B2:B17) in cell B18.
Learning some the many functions and there use can be daunting. One way to get specific help is
to have a trainer set-up training classes, on specific subjects, at your Company. Check my website
(http://www.lwmweb.com/ ) for upcoming seminars or contact me ( LWM@LWMWEB.COM) for
in-house seminars for your company. On the other hand, you can study the many Excel books
currently available.

Copyright 2007 Larry Moseley

You might also like