# Sumproduct with multiple row and column criteria

#### truebluewoman

##### New Member
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:

Excel Formula:
``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
Excel Formula:
``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
Excel Formula:
``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.

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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.

Excel Formula:
``````=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)``````

Replies
3
Views
234
Replies
5
Views
229
Replies
6
Views
359
Replies
3
Views
392
Replies
16
Views
1K

1,203,736
Messages
6,057,066
Members
444,903
Latest member
Mavericx

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back