Using SUMIF to exclude mulitple criteria

[stevewhite00]

I have a list of stocks, all with industry groups. I use SUMIF to su
mkt caps of each industry, which is no problem. However, I also want
"miscellaneous" category, whereby I could use SUMIF to sum cells tha
do not meet a certain criterias - not just one. I cannot get it t
work. What I've tried:

=SUMIF(A:A, OR("<>Biotech", "<>Retail", "<>Semis"), D)

I thought this would work, using the "OR" function, but it hasnt.
cant get SUMIF to work using multiple criteria.

Thank

 

[Andy B]

Hi

Try using SUMPRODUCT:
=SUMPRODUCT((A2:A1000<>"Biotech")*(A2:A1000<>"Retail")*(A2:A1000<>"Semis")*(
D21000))

 

[stevewhite00]

That worked, thanks.

The one strange thing though is that it's not letting me use the "*"
qualifier. Any idea why this is happening?

Thank

 

[Andy B]

I don't know this is happening - it always does the same for me!!

 

[Aladin Akyurek]

Enter the criteria to exclude in a range, say in X2:X4, while not a
prerequisite, sort X2:X4 in ascending order, and try:

=SUMPRODUCT(--($A$2:$A$100<>""),-ISNA(MATCH($A$2:$A$100,$X$2:$X$4,0)),$D$2:$
D$100)