In one of my most popular posts 5 Excel Functions That Every Logistics Manager Should Know I provided practical examples of the VLOOKUP, TRIM, CONCATENATE, NETWORKDAYS Excel functions and instructions on how to calculate the WEEK BEGINNING DATE in Excel.
In this post I look at a 3 More Excel Functions That Every Logistics Manager Should Know – these include:
As per my previous post I have also created a spreadsheet that provides some real life examples of each of the below mentioned Excel functions – to download a copy of the spreadsheet click on the link below
SUMIF and SUMIFS EXCEL FUNCTIONS
These functions allow you to sum values based on specified conditions. The SUMIF function will sum values based on one condition whereas the SUMIFS excel function allows you to specify multiple conditions and will only calculate the total if all conditions apply. As an example, you can use them to calculate the totals for specific time periods, cost categories or service types etc.
Syntax =SUMIF(Condition Range, Condition, Sum Range)
e.g. =SUMIF($C$11:$C$41,H6,$D$11:$D$41)
Syntax =SUMIFS(Sum Range, Condition Range1, Condition1, Condition Range2, Condition2)
e.g. “=SUMIFS($D$11:$D$41,$A$11:$A$41,I6,$C$11:$C$41,G7)
COUNTIF and COUNTIFS EXCEL FUCNTIONS
These functions allow you to count the number of cells that meet specified conditions. The COUNTIF function will COUNT values based on one condition whereas the COUNTIFS excel function allows you to specify multiple conditions and will only count the value in a cell if all conditions apply As an example they can be used to count the occurrences of specific time periods, cost categories, service types etc.
Syntax COUNTIF(Condition Range, Condition)
e.g. “=COUNTIF($C$11:$C$41,B6)
Syntax =COUNTIFS(Condition Range1, Condition1, Condition Range2, Condition2)
e.g.”=COUNTIFS($A$11:$A$41,D5,$C$11:$C$41,B6)
AVERAGEIF and AVERAGEIFS EXCEL FUNCTIONS
Similar to SUMIF, SUMIFS, COUNTIF and COUNTIF functions these functions calculate average values based on specified conditions. The AVERAGEIF function will calculate average values based on one condition whereas the AVERAGEIFS excel function allows you to specify multiple conditions and will only calculate the average if all conditions apply. As an example you can use them to calculate the totals for specific time periods, cost categories, service types etc.
Syntax = AVERAGEIF(Condition Range, Condition, Sum Range)
e.g.”=AVERAGEIF($C$11:$C$41,H6,$D$11:$D$41)
Syntax =AVERAGEIFS (Sum Range, Condition Range1, Condition1, Condition Range2, Condition2)
e.g. “=AVERAGEIFS($D$11:$D$41,$A$11:$A$41,I6,$C$11:$C$41,G7)
The AVERAGEIF function is also useful to use if you want to calculate the AVERAGE in a range that contains blank cells.
To download a copy of a spreadsheet provides real life examples of each of the Excel functions discussed above – click on the link below
Leave a Reply