How to exclude values in a list with SUMPRODUCT?

[orangehenry]

Hi,

Is there a simple way to get sumproduct to exclude based on values in a list.

I.e. =SUMPROUDUCT(--(source!$A:$A<>exclusions!$A$1:$A$10),source$B:$B)?

Figure you should be able to do with a match or something but cant seem to find it.

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

[Exl-Noob]

(well you uploaded a sample file long after I started working on a solution, therefore I'm not using it)

Hmm. It should be possible. It would be less likely without a sample excel file

To my knowledge you'd have to refer to each individual exclusion item rather than a list.
Therefore the only solution i can come up to would look either like :

Please Login or Register  to view this content.

Or

Please Login or Register  to view this content.

See following sample file if needed.
You could extend the formulas to your likings.

[Exl-Noob]

In your example file, this would work

Please Login or Register  to view this content.

as would also this work if you want to add more empty cells in case your exclusion list grows :

Please Login or Register  to view this content.

[Glenn Kennedy]

this is the "textbook" way of doing this:

=SUMPRODUCT(--(ISERROR(MATCH(B4:B9,H4:H5,0))),C4:C9)

[Exl-Noob]

Whoa
slow clap

[wk9128]

Try