Using SUMIF to exclude mulitple criteria

  • Thread starter Thread starter stevewhite00
  • Start date Start date
S

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:D)

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

Thank
 
Hi

Try using SUMPRODUCT:
=SUMPRODUCT((A2:A1000<>"Biotech")*(A2:A1000<>"Retail")*(A2:A1000<>"Semis")*(
D2:D1000))
 
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
 
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)
 
Back
Top