For this week’s discussion, I have decided to discuss how the SUMIF function is used for calculating individual hours contributed to a project. The SUMIF function is used often for financial work, such as budgeting or manpower allocation. It is a math function based on operators such as less than <, greater than >, less than or greater than < >, and equal to = just for a few examples but also considers other functions such as * and ?.
As with all functions within Excel it requires a formula that must meet certain criteria. In the case of SUMIF the formula must contain the following:
These are the cells we will apply the criteria to.
The provided criteria can be:
“<5”, “=20”, “= or < 32”
A numeric value
Integer, decimal, logic value for example
or optionally you can include a Sum range which is a group of cells that are to be added together if the range entry meets a supplied criterion.
One example of how the SUMIF function works would be if you want to calculate how many hours individual employees contributed to a project you can use SUMIF to select a particular name, word, or phrase from a given range of cells and then add the given number in another range of cells and place the “SUM” in a separate cell. As seen in the examples below.
So, in this case, we are instructing it to SUM the inputs from L3 thru L8 if K3 thru K8 contains the phrase “POWER CABLE” and place the SUM in O11 or if it the phrase says “CAN CABLE” place the SUM in M11. It is important to make sure that the text criteria or criteria that include math symbols be contained with quotations “ “.
I have learned that excel can be a great tool to make our jobs simpler and more productive and I have also found it to be very user friendly, in fact, what I have learned about excel up to this point has been through the use of Google where there is a bunch of great how To’s and this is a tool I use almost daily from the simplest of tasks, counting the characters in a part name as we are restricted to forty where I work, to organizing wide branching projects I am working on. That being said, I look forward to learning more about the tool in this class.
CFI Team, (2022) SUMIF Function - Formula, Examples, How to Use SumIf in Excel (corporatefinanceinstitute.com)