SUM(COUNTIF(range,NOT Criteria))
 

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",<"criteri a2",<"etc"}))}

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

SUM(COUNTIF(range,NOT Criteria))
 

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

"Santa-D" wrote in message
ups.com...
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",<"criteri a2",<"etc"}))}

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

SUM(COUNTIF(range,NOT Criteria))
 

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!
==============================================


"Santa-D" wrote in message
ups.com...
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",<"criteri a2",<"etc"}))}

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

SUM(COUNTIF(range,NOT Criteria))
 

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","Criteria 3"})

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

Hope this helps!

In article . com,
"Santa-D" wrote:

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",<"criteri a2",<"etc"}))}

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