SUM(COUNTIF(range,NOT Criteria))

[Santa-D]

I'm currently using a CSE formula to retrieve the total number of
different criteria within a data source using the following:

{=SUM(COUNTIF(data.range,{"criteria1","criteria2","etc"}))}

What I want to do is now sum the values of those items that are not of
those values i.e.

{=SUM(COUNTIF(data.range,{<>"criteria1",<>"criteria2",<>"etc"}))}

Is this possible and if yes, how do I go about doing it.

 

[Biff]

Hi!

Your formula does not need to be CSE entered.

Maybe something like this: (depends on details that were not provided)

=ABS(COUNTA(A1:B5)-SUM(COUNTIF(A1:B5,{"a","b","c"})))

Biff

 

[RagDyer]

First of all ... you *don't* need an array entry (CSE) for your count
formula!

And, one way to approach your totaling of values that do not match the
criteria is:

=SUM(range,-SUMIF(range,{criteria1,criteria2,criteria3}))
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[Domenic]

Try...

=SUMPRODUCT(--(A1:A10<>""),(1-ISNUMBER(MATCH(A1:A10,{"Criteria1","Criteri
a2","Criteria3"},0))))

By the way, your first formula...

=COUNTIF(A1:A10,{"Criteria1","Criteria2","Criteria3"})

....does not need to be confirmed with CONTROL+SHIFT+ENTER. You only
need to confirm with ENTER.

Hope this helps!