OR conditions in a SUMPRODUCT formula

I am using SUMPRODUCT to build a stats page for a report. I need a lot of different combos of the data and SUMPRODUCT usually works like a charm for me. But I'm stuck. In this particular case I have two columns, each containing multiple values. I want to pull data for a subset of values from each of those columns (using "<>" multiple times will be just as onerous).

I'd like to try to keep it simple.... and had hoped that something like this would work. But this returns an error.
=SUMPRODUCT((Severity={"BI","NI","XY"})*(Type<>"Duplicate")*(Time<5)*(Source={"A1","A6","A7","A12"}))

I can get it to work with this option but this means that my formulas will become very long.
=SUMPRODUCT((Severity="BI")*(Type<>"Duplicate")*(Time<5)*(Source={"A1","A6","A7","A12"}))+SUMPRODUCT((Severity="NI")*(Type<>"Duplicate")*(Time<5)*(Source={"A1","A6","A7","A12"}))+SUMPRODUCT((Severity="XY")*(Type<>"Duplicate")*(Time<5)*(Source={"A1","A6","A7","A12"}))

I tried using an OR condition for the Severity but that gave me the wrong value. I played around a bit thinking it might need to be treated as an array formula, but that was a disaster. I feel like I'm missing something simple.... or that I really will have to build long formulas.

Any ideas or suggestions?

Without a sample for testing, not sure it can help, but try:

=SUMPRODUCT(--((((Severity="BI")+(Severity="NI")+(Severity="XY"))=2)*(Type<>"Duplicate")*(Time<5)*(((Source="A1")+(Source="A6")+(Source="A7")+(Source="A12"))=2)))

Sorry, may be I 've made wrong formula with previous post, pls update:

=SUMPRODUCT(--((((Severity="BI")+(Severity="NI")+(Severity="XY")+(Source="A1")+(Source="A6")+(Source="A7")+(Source="A12"))=2)*(Type<>"Duplicate")*(Time<5)))

Thanks for the rep. Good luck!

Originally Posted by razz0807

=SUMPRODUCT((Severity={"BI","NI","XY"})*(Type<>"Duplicate")*(Time<5)*(Source={"A1","A6","A7","A12"}))

Are the named ranges 1 dimensional ranges? (single row or single column)?

Assuming all the named ranges have the same dimensions you can use COUNTIFS like this

=SUM(COUNTIFS(Severity,{"BI","NI","XY"},Type,"<>Duplicate",Time,"<5",Source,{"A1";"A6";"A7";"A12"}))

Note: for this type of formula you can't have more than 2 "multi-item" criteria - also one of the arrays needs to be separated by commas and the other by semi-colons, hence {"A1";"A6";"A7";"A12"} in my version rather than {"A1","A6","A7","A12"}