The Dashboard's $$Line Items$$ dump sheet only contains the unit costs for the Labor, Material, Equipment, Sub, and Other cost categories. How can I sum up the total cost for one of these cost categories for certain line items in the estimate?
This is a slight extension to the process of summing up the total cost of a cost category for the entire estimate. A post explaining that process can be found here...
How can I sum up the total cost of a cost category in my dashboard
From there, it's a matter of "filtering out" the line items you don't want to sum up.
Unfortunately, SUMPRODUCT does not have an equivalent SUMIF. We need to be a little more creative in order to make this work. We do this by pulling in a couple more functions -- ISNUMBER and SEARCH. These functions will allow us to effectively filter outline items based on criteria.
Assume we only want to sum up the Labor Cost for line items that belong to the system "Office" and that the System is found in Column B. I would write the function below...
=SUMPRODUCT(--(ISNUMBER(SEARCH("Office",'$$Line Items$$'!B:B))),'$$Line Items$$'!F:F, '$$Line Items$$'!Y:Y)
The introduction of the ISNUMBER and SEARCH functions adds a slight twist to the SUMPRODUCT call by introducing either a 1 or 0 into the multiplication of each row. If the System column does not contain the text "Office", the row will be multiplied by 0 -- otherwise, it will be multiplied by 1. This effectively gives our SUMPRODUCT function the ability to filter outline items.
You can use the same approach for each cost category you want the total cost for.