Sumproduct with multiple row and column criteria

[truebluewoman]

I have been trying to solve this for about a week and cannot seem to figured this out. I have several sections of a worksheet that is for 10 types of work being performed, the status of the work, and an estimate of the cost of that work. It looks like this, but has a lot of other items in between (like Contractor, date scheduled, etc), so for the sake of brevity, I am simply showing the basic structure of the data:

Unit Number	Estimated Cost	Status	Estimated Cost	Status	Estimated Cost	Status
Unit 1	150.00	In Progress	300.00	Completed	0.00	No Turn
Unit 2	250.00	Completed	200.00	Completed	300.00	Completed
Unit 3	0.00	No Turn	0.00	No Turn	15.00	In Progress
	Paint	Paint	Cleaning	Cleaning	Carpet Repair	Carpet Repair

I am trying to sum the cost of the Paint Items that have been completed. I am able to write a formula for the overall estimated cost. That formula is the following:

SUM(INDEX(COSTESTIMATES,,MATCH("Estimated Cost"&$B13,'5 - Contractor Schedule'!$A$6:$DF$6&'5 - Contractor Schedule'!$A$541:$DF$541,0)))

I cannot seem to be able to figure out how to limit the rows to only those that are in "Completed" status. I thought that if I could insert the row numbers in between the two commas(after the "COSTESTIMATES" but before the "MATCH", it would then limit the results to those particular rows.

I have also tried using AGGREGATE, SUMIFS, and SUMPRODUCT, but I could not write anything that would result in what I need.

The following two SUMPRODUCT formulas give me each component, but I cannot combine them to limit the totals. I get zeros or VALUE! errors:

Sums the totals of the estimated costs for paint items

SUMPRODUCT(('5 - Contractor Schedule'!$A$541:$DF$541=$B13)*('5 - Contractor Schedule'!$A$6:$DF$6="Estimated Cost")*ISNUMBER(COSTESTIMATES),COSTESTIMATES)

Returns the number of completed paint items

SUMPRODUCT((COSTESTIMATES="Completed")*('5 - Contractor Schedule'!$A$541:$DF$541=$B13)*('5 - Contractor Schedule'!$A$6:$DF$6="Status"))

COSTESTIMATES= Named Range '5 - Contractor Schedule'!$A$6:$DF$536
$B13 = 'Paint'

If anyone can help me with this, I would be eternally grateful.

 

[truebluewoman]

This was a tough one, but I figured this out by using FILTER with SUMPRODUCT.
The first FILTER gives me the completed and the second filter gives me the costs. I ended up tweaking the named range to exclude the headers as they did not need to be included in the actual range to be utilized.

=IFERROR(SUMPRODUCT((FILTER(CONTRACTORCOSTS,('5 - Contractor Schedule'!$A$541:$DF$541=CONCAT($B13," ",$C13))*('5 - Contractor Schedule'!$A$6:$DF$6="Status"))="Completed")
*(FILTER(CONTRACTORCOSTS,('5 - Contractor Schedule'!$A$541:$DF$541=CONCAT($B13," ",$C13))*('5 - Contractor Schedule'!$A$6:$DF$6="Estimated Cost")))),0)