use array formula with or function

Try this & change the ranges as required

=SUM(IF($G$1:$G$30={7,8,9},$E$1:$E$30,0))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

You can also make a non-CSE version of NeedForExcel's CSE solution:

=SUMPRODUCT(($G$1:$G$30={7,8,9})*$E$1:$E$30)

Also, be strongly advised not to use entire column references within an array formula.

Unlike some functions, e.g. COUNTIF(S), SUMIF(S), with which the use of entire column references has virtually no detriment to calculation performance, array-processing functions must calculate over all cells passed to them. And that applies equally to those cells which are beyond the last non-empty cell in the range being passed.

So if, for example, you only have data extending as far as row 1000, then, by using entire column references in such a formula, you are forcing Excel to calculate more than one million cells beyond that which is actually necessary, an astonishing amount for a single formula.

As such, it is important to either choose a suitably low - though sufficient - value for the upper row reference, or else make your ranges dynamic, such that the upper row reference is determined via a function which automatically detects the last-used cell within the column, thus offering maximum efficiency.

Regards

You can also make a non-CSE version of NeedForExcel's CSE solution

Which is always better than a CSE Formula.. I have a very bad habit of writing CSE Formulas, even when non-cse can be written.. Need to get rid of the habit ASAP

Originally Posted by cbatrody

=SUMPRODUCT(SUMIF(G3:G11,B3:B5,E3:E11))

Even better!

Originally Posted by ronga

I'm still wondering why my original approach did not work.

OR only ever returns a single value, never an array of values.

Regards