Forum Discussion
Solved
Summing values with unique properties
Hello! My table is copied below. I am wanting to sum the dollar amounts in column Estimate Value per each scope of work, but only include one of the scope of work values per project name, but then exclude all dollar amount values where "Contractor Not Awarded" is the value in the Status column. For example, the dollar amount for the Sheetmetal scope of work that is filtered by one "Sheetmetal" per Project Name and not including values where the status is "Contractor Not Awarded" comes out to $54,020. This the resulting value that I am after.
I have the current formula written that does everything I want, except for excluding the Status values that equal "Contractor Not Awarded." Here is my formula =IFERROR(SUM(INDEX(D5:D35,XMATCH("Sheetmetal" &UNIQUE(FILTER(A5:A35,C5:C35="Sheetmetal")),C5:C35&A5:A35))),0). This formula produces a value of $79,040.
Any help in figuring out how to alter my current formula or create a new one is greatly appreciated!
-Zach
| A | B | C | D | E | F | |
| 2022 | ||||||
| PROJECT NAME | CONTRACTOR | SCOPE OF WORK | ESTIMATE VALUE | MONTH OF ESTIMATE | STATUS | |
| Bridge of Grace | PDP | Plumbing and Mech Pipe | $8,865.00 | September | Contractor Not Awarded | 5 |
| Bridge of Grace | PDP | Sheetmetal | $8,100.00 | September | Contractor Not Awarded | 6 |
| Bridge of Grace | Current | Plumbing and Mech Pipe | $8,865.00 | September | Contractor Not Awarded | 7 |
| Bridge of Grace | Current | Sheetmetal | $8,100.00 | September | Contractor Not Awarded | 8 |
| Bridge of Grace | Hattersley | Plumbing and Mech Pipe | $8,865.00 | September | Contractor Not Awarded | 9 |
| Bridge of Grace | Hattersley | Sheetmetal | $8,100.00 | September | Contractor Not Awarded | 10 |
| Electric Works Apartments A | LA-Electric | Electrical | $9,400.00 | July | Weigand Not Successful | 11 |
| Levan Scott | Hattersley | Plumbing and Mech Pipe | $20,100.00 | July | Contractor Not Awarded | 12 |
| Levan Scott | Hattersley | Sheetmetal | $11,000.00 | July | Contractor Not Awarded | 13 |
| Levan Scott | PDP | Plumbing and Mech Pipe | $20,100.00 | July | Weigand Not Successful | 14 |
| Levan Scott | PDP | Sheetmetal | $11,000.00 | July | Weigand Not Successful | 15 |
| Norwell Middle School | Hattersley | Plumbing and Mech Pipe | $63,360.00 | February | Contractor Not Awarded | 16 |
| Norwell Middle School | PDP | Plumbing and Mech Pipe | $63,360.00 | February | Weigand Not Successful | 17 |
| Norwell Middle School | PDP | Sheetmetal | $32,400.00 | February | Weigand Not Successful | 18 |
| Norwell Middle School | Kissinger | Electrical | $15,840.00 | March | Weigand Successful | 19 |
| Steuben Co. Courthouse | PDP | Plumbing and Mech Pipe | $18,720.00 | September | Contractor Not Awarded | 20 |
| Steuben Co. Courthouse | PDP | Sheetmetal | $10,620.00 | September | Weigand Turned Down | 21 |
| Steuben Co. Courthouse | Hattersley | Plumbing and Mech Pipe | $18,720.00 | September | Contractor Not Awarded | 22 |
| Steuben Co. Courthouse | Hattersley | Sheetmetal | $10,620.00 | September | Weigand Turned Down | 23 |
| Steuben Co. Courthouse | Current | Plumbing and Mech Pipe | $18,720.00 | September | Weigand Successful | 24 |
| Steuben Co. Courthouse | Current | Sheetmetal | $10,620.00 | September | Weigand Turned Down | 25 |
| Steuben Co. Courthouse | Kissinger | Electrical | $7,500.00 | October | Weigand Successful | 26 |
| The Pearl | Hattersley | Plumbing and Mech Pipe | $22,000.00 | June | Weigand Not Successful | 27 |
| Whitley Co. Jail | PDP | Plumbing and Mech Pipe | $20,520.00 | November | Contractor Not Awarded | 28 |
| Whitley Co. Jail | PDP | Sheetmetal | $16,920.00 | November | Contractor Not Awarded | 29 |
| Whitley Co. Jail | Hattersley | Plumbing and Mech Pipe | $20,520.00 | November | Contractor Not Awarded | 30 |
| Whitley Co. Jail | Hattersley | Sheetmetal | $16,920.00 | November | Contractor Not Awarded | 31 |
| Whitley Co. Jail | Kissinger | Electrical | $8,700.00 | November | Contractor Not Awarded | 32 |
| 33 | ||||||
| 34 | ||||||
| 35 |
=IFERROR(SUM(INDEX(D5:D35,XMATCH("Sheetmetal" &UNIQUE(FILTER(A5:A35,(C5:C35="Sheetmetal")*(F5:F35<>"Contractor Not Awarded"))),C5:C35&A5:A35))),0)
This returns the expected result in my sheet.
=IFERROR(SUM(INDEX(D5:D35,XMATCH("Sheetmetal" &UNIQUE(FILTER(A5:A35,(C5:C35="Sheetmetal")*(F5:F35<>"Contractor Not Awarded"))),C5:C35&A5:A35))),0)
This returns the expected result in my sheet.
- That worked! Seems like a simple solution but you don't know how long I have been stumped on this. I really appreciate it!
