CODEWITHSUNDEEP
excelms-office
programad
programad

Reputation: 1311

Sum if multiple negative criteria

I know this sounds lame, but I'm struggling with Microsoft Excel to make this formula work.

I need to sum numbers if matches two criterias, one positive and one negative.

The positive critirea is ok, but the negative one must be "if the cell text is not 'ACTIVE'). How to acomplish this?

This is the formula (with both positive criterias) I have so far:

=SUMIFS($M$8:$M$100819;$C$8:$C$100819;CONCATENATE(TEXT(D$1;"0");"*");$S$8:$S100819;"ACTIVE")

I need to negat that "ACTIVE", and I don't know how and google itself is not helping very much.

Upvotes: 1

Views: 1771

Answers (2)

Brian Camire
Brian Camire

Reputation: 4825

Another soultion would be to add <> to the criteria, as in:

=SUMIFS($M$8:$M$100819;$C$8:$C$100819;CONCATENATE(TEXT(D$1;"0");"*");$S$8:$S100819;"<>ACTIVE")

Upvotes: 4

Hakan Serce
Hakan Serce

Reputation: 11256

Just use : 

<SUM_OF_ALL_ITEMS_WITH_FIRST_CRITERIA> - SUMIFS($M$8:$M$100819;$C$8:$C$100819;CONCATENATE(TEXT(D$1;"0");"*");$S$8:$S100819;"ACTIVE")

Upvotes: 2

Related Questions