Excel tip

There are certain functions every PM should know.  The first is sum().  This is taught in every introductory excel class and most people generally know how to use it.  To be a good PM you need the ability to use sumif() and sumifs().

These two functions work just like sum() but they allow you to set criteria for what is things will be added to the sum.  For example lets say you have data that looks like this.

1 July 1 Blue
2 Aug 2 Red
3 July 3 Blue
4 March 4 Red
5 Aug 5 Blue
6 Aug 6 Blue
7 July 2 Red
8 March 1 Red

Now you are asked to add up the numbers in column B for July. The formula =sumif(the range that has the month in it, the month you want to sum by, the range that has the numbers to sum). Or in this case =sumif(A1:A8, “July”, B1:B8) will produce the correct value. The you could also directly reference the lookup value by doing something like =sumif(A1:A8, A2, B1:B8).

Now if you need all the numbers in column B added together but only for the month of Aug where the value in column C is Blue then you would use sumifs(). Which basically is sumifs(what column has the numbers you want to add together, what range has the first criteria, what is the first criteria, what range has the second criteria, what is the second criteria, ect) you can use as many criteria as you would like. In this example the function would look like this sumifs(B1:B8,A1:A8,”Aug”,C1:C8,”Blue”). That will return the correct value of 11.

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s