Question of the Week – DESTINI Estimator: How do I use a SUMIFS Statement?
Questions of the Week are asked and answered in the Beck Technology Community. Anyone is encouraged to join the community to discover additional tips and tricks.
This answer is provided by Casey Wilt, implementation specialist, and posted on our community as well as below.
First, a definition:
SUMIFS functions will sum up the selected cells if all criteria in the equation, whether ONE or MULTIPLE, are met.
SUMIFS(This Range, If this Condition, and this one, and this one are all met)
We’ll start with a simple one-condition SUMIFS and then we’ll move on to a more advanced scenario:
In this example, we are going to write to formula for cell B4, for A Substructure. The line item data dump is found on the $$Line Items$$ sheet, so we’ll be linking each criteria to cells on that sheet in the workbook.
In detail, the SUMIFS statement shows the following arguments in Excel…
SUMIFS(Target Range, Criteria Range One, Criteria)
In English, that reads as…
SUMIFS(Add the cells, whose value in these sections, matches this criteria)
SUMIFS(“$$Line Items$$ Aggregate Cost Column”, “$$Line Items$$ Uniformat Level 1 Column”, “Cell A4”)
The Aggregate Cost column for this dashboard is Z, and the Uniformat Level 1 column is BP.
Therefore, using normal cell linking, our B4 Formula should be:
=SUMIFS(‘$$Line Items$$’!Z:Z,’$$Line Items$$’!BP:BP,$A4)
When you press enter, you should see…
The cool thing about how this formula was written is you can simply drag that formula down to the other cells in the table and it will calculate their values as well…
Now, let’s take things up a notch or 3…
A very powerful dashboarding tool is looking at your WBS breakdown per location in the project.Our default location WBS is called “System”, so let’s break this project down into it’s appropriate parts. We will get an awesome data analysis tool, and a great visual…. all by adding one more condition to the formula we already wrote.
Check out the new dashboard…
Our existing formula really needs just one more condition to make this example work nicely.
The System column is B:B in $$Line Items$$, and it needs to match either B1 or C1.
SUMIFS(….. , …… , ‘$$Line Items$$’!B:B, $B$1) or SUMIFS(….. , …… , ‘$$Line Items$$’!B:B, $C$1)
* notice that column and row are locked in the final criteria, so that the reference is still to the system name as you copy down the formula*
When you add these conditions (italicized) to the existing formula in A5 and drag it down, you’ll see that total prices changes. Creating the Column D version populates those costs in the same fashion.
Final Dashboard, With final formulas shown.
And finally, a teaser, just to show what is possible with this logic… Try to take yours to this level!! Don’t hesitate to reach out with questions.