Question of the Week – DESTINI Estimator: How do I take advantage of TRUE/FALSE WBS properties?

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.

How do I take advantage of TRUE/FALSE WBS properties?

This answer is provided by Casey Wilt, implementation specialist, and posted on our community as well as below.

SUMIFS statements, covered in another post, give us the ability to run a sum given multiple criteria. This post focuses on the development of one particular type of criteria you can use.

The line item data from the Estimate View is essentially “exported” to the Dashboard view within the Estimator program. When that data is populated on the $$_____$$ Sheets, there are some formatting “corner cases” that you run into. The vast majority of the data responds as expected to standard Excel logic, but the TRUE/FALSE WBS type is one of those unique instances.

When you try to use the “True” value as a criteria in your SUMIFS, the logic essentially reads as follows:
Only give me the sum of these 03 Concrete items if their “Allowances” value is checked as “True”.

=SUMIFS(…… , ….. ,’$$LineItems$$’!BP:BP, “True”)

By typing “True” within quotes, you are asking it to look for precisely that text in column BP, which you can see exists below. (I formatted the text to make it easier to see)

line items with one true value

Unfortunately, this didn’t work, did it? The column is text, but it doesn’t respond as expected. If you just type True without the quotes, the formula attempts to reformat that word to TRUE as the Boolean function. Which, as you might imagine, doesn’t work because the BP column doesn’t show Boolean functions either.

Where does that leave us? We simply have to outsmart Excel logic and formatting that are attempting to be overly-helpful.

The * symbol serves as a wildcard symbol in excel, and it is our ticket to success!

=SUMIFS( _____ , _____ ,’$$LineItems$$’!BP:BP,“=T*”)

It’s that simple! Basically we say, regardless of formatting, if this cell’s value starts with T, we want that included. Works like a charm.

There is one more step we can take to make sure that future updates to the toolkit don’t break the link in this dashboard. We’ll include a second option for the Boolean version of TRUE. While this looks trickier, think of it as running two SUMIFS in the same cell as the same time – literally, the SUM of two SUMIFS.

SUM(SUMIFS( ________, ___________, ‘$$LineItems$$’!BP:BP,{=T*”, TRUE}))

Paste that formula into you cell and you’ll have the results you were looking for! (Notice the $25,000 matches that first excel document I shared.)

SUMIFS formula and correct result