Excel, sum if a row value matches a vlookup value on another sheet

Question

Is it possible to sum all values in a column, based on a criteria checked against on another sheet?

I have two sheets, one with values, one with manufacturers. I would like check if the manufacturer has any value other than "YES" on another sheet and then sum if not equal to "YES". The end result is a total for all manufacturers that do NOT have a value of "YES" on Sheet 2.

In the example below I am trying to sum everything EXCEPT for Manuf B, a total of $1,132.

So far the formula I was working on (non functional) was:

=sumif(vlookup(C5:C12,'SHEET 2'!E5:F8,2,false),not("YES"),B5:B12)

Answer 1

I think this is a bit cleaner...

=SUM(SUMIF(C5:C12,IF(Sheet2!F5:F8<>"YES",Sheet2!E5:E8,FALSE),B5:B12))

It's still an Array Formula so you have to hit Ctrl + Shift + Enter instead of just Enter when you add the formula

Answer 2

As an array formula with CSE,

=SUMPRODUCT(B5:B12,--ISNUMBER(SEARCH(C5:C12, TEXTJOIN(",",TRUE,IF(F5:F8<>"yes",E5:E8)))))

... or a (possibly) more conventional array formula (with CSE),

=SUM(SUMIFS(B5:B12, C5:C12, TRANSPOSE(INDEX(E5:E8&IF(F5:F8="yes", CHAR(215), TEXT(,)), , ))))

Slightly abbreviated version of the latter array formula,

=SUM(SUMIFS(B5:B12,C5:C12,INDEX(IF(F5:F8="yes",CHAR(215),E5:E8),,)))