Sumif or Sumproduct with index and match

[tpedry]

I need the formulas in P5:P18 to be the sum of "Table25[Total Estimate Cost] ", minus any rows that are Job Options listed in column B

The code below will do excatly opposite of what I want to do. I do not want to include any OPT1, OPT2, etc..

Please Login or Register  to view this content.

here is the sumif formula that worked before I created the new index, match formula above.

Please Login or Register  to view this content.

Screen Shot 2015-03-21 at 5.07.20 PM.png

Screen Shot 2015-03-21 at 4.56.09 PM.png
Screen Shot 2015-03-21 at 4.59.05 PM.png

[gmr4evr1]

Cant see/use the pics you posted, could you post a sample workbook with what you are getting and the expected results?

[daddylonglegs]

Does this work?

=IFERROR(SUMIF(Table25[Division],"<>OPT*",Table25[Total Estimate Cost]),0)

[tpedry]

Here is an example workbook.

Sumif workbook.xlsx

[tpedry]

Daddylonglegs,

that does work for getting rid of the Job Option rows, but the formula still adds all the other rows because it does not contain the other criteria of matching the "Major", column C and M.

[tpedry]

Is this what I am looking for?

Please Login or Register  to view this content.