Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Returning Results Based on Two Criteria | Excel Worksheet Functions | |||
Find largest alphanumeric value matching alpha criteria in databas | Excel Worksheet Functions | |||
sorting more than 3 keys | Excel Discussion (Misc queries) | |||
Can I get the mode, min, and max with multiple criteria? | Excel Discussion (Misc queries) | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions |