Sum and Countifs using a Not Equal logic

[spydey]

This should be quite simple but for some reason the correct output is evading me.

I have a cell with a formula in it, I would like the output to give me a count of all cells in a range that DO NOT meet the items listed in the array.

Here is an example:

=SUM(COUNTIFS($A$2:$A$500,"<>"&{"Complete","Pending"},$C$2:$C$500,$A$1,$C$2:$C$500,EOMONTH($A$1,0)))

So basically I need to know how many items there are in Column A that are NOT Completed or Pending and fall within a certain date range, found in column C.

I figured I could simply use the "<>" not equal to logic indicator and it would work, but I was wrong.

Any thoughts to steer me in the right direction?

Thank you!

-Spydey

 

[Jonmo1]

No, you can't use that syntax with <> criteria.
Because it's an OR criteria.
So your formula is saying
count if
Col A Does Not Equal Complete
OR
Col A Does Not Equal Pending

At least One of those 2 criteria will ALWAYS be true.

Just add a 2nd standard criteria to the normal countifs

=COUNTIFS($A$2:$A$500,"<>Complete",$A$2:$A$500,"<>Pending",$C$2:$C$500,$A$1,$C$2:$C$500,EOMONTH($A$1,0))

 

[spydey]

@ Jonmo1,

Thanks for the response, very much appreciated.

I knew that I could do it that way, but I was hoping to avoid it. My actual spreadsheet has like 50+ different statuses in Column A. I was hoping to just list the 10 I don't need counted, once in an array, use the Does Not Equal indicator, and call it good, so that I was given an output of everything other than the 10 or so that I don't need. If I have to list all 10 and the corresponding ranges for each one, that makes for a big and dirty looking formula. I guess I was just being lazy to a degree, lol.

And you are correct sir, I was using the OR criteria incorrectly by doing that way.

I really thought I could use an array somehow and give me an output of everything BUT the items listed in the array ... oh well .... I guess I will have to take a look at some different options .... or just bite the bullet and write it all out, cell ranges and everything .... (yuck!!)

Thank you again for your help, I appreciate it very much.

-Spydey

 

[Jonmo1]

In that case, you can use reverse logic...

Say you have possible values of A B C D E F and G.
You want the ones that DO NOT = B C or D

We know this syntax only works for OR Right?
=SUM(COUNTIFS(range,{"B","C","D"}))
This gives you the count of the ones that DO equal B C or D.

Logic says that the count of the ones that do not equal B C or D is equal to the (count of ALL) minus (count of B C and D)

So COUNT OF ALL
=COUNTIFS($C$2:$C$500,$A$1,$C$2:$C$500,EOMONTH($A$1,0)))

COUNT of the ones you DO NOT WANT to count
=SUM(COUNTIFS($A$2:$A$500,{"Complete","Pending"},$C$2:$C$500,$A$1,$C$2:$C$500,EOMONTH($A$1,0)))

Put it together
=COUNTIFS($C$2:$C$500,$A$1,$C$2:$C$500,EOMONTH($A$1,0)))-SUM(COUNTIFS($A$2:$A$500,{"Complete","Pending"},$C$2:$C$500,$A$1,$C$2:$C$500,EOMONTH($A$1,0)))

 

[barry houdini]

=SUM(COUNTIFS($A$2:$A$500,"<>"&{"Complete","Pending"},$C$2:$C$500,$A$1,$C$2:$C$500,EOMONTH($A$1,0)))

Hello spydey,

Another option here is to switch to SUMPRODUCT and use an ISNA(MATCH construction to exclude a list, e.g. for the above

=SUMPRODUCT(--ISNA(MATCH($A$2:$A$500,{"Complete","Pending"},0)),--($C$2:$C$500=$A$1),--($C$2:$C$500=EOMONTH($A$1,0)))

You can list all the items to exclude as above or use any single column/row range like Z2:Z10

 

[spydey]

Yes, of course, reverse logic! I hadn't thought about that. Thank you Jonmo1 for pointing that out and for providing a formula too. You're awesome and I appreciate your assistance. I will just have to take that formula and adjust it to my actual cells, ranges, and criteria.

Take care!

-Spydey

 

[spydey]

Barry,

I have used SumProduct for other formulas but nothing quite like what you pointed out. Also, I have never used ISNA and/or MATCH before, so this was a good example to help me understand how they work together. Thanks for posting that, I appreciate it.

I am going to give it a whirl and see if I can edit it for my actual ranges, cells, and criteria.

Take care!

-Spydey

 

[spydey]

@ Barry,

I tried using the formula you provided, but I think that perhaps either I missed something or didn't input it correctly. It kept giving me a match for everything. Also, something that I didn't mention, which might affect the output, I have use wildcards, i.e. "*Complete*", "*Pending*", etc. Would that affect the output at all? Are wildcards permissible while using the various nested functions?

I think I will play around with it a bit more and see if I can get it to work. I would really like to get it to work so that I can add it to my "arsenal" of Excel functions .... I feel that I may be needing it sometime soon ....


@Jonmo1,

I gave it a whirl, adjusting to my actual parameters, and although it was lengthy to initial write, it worked wonderfully! Thank you again for your help.

-Spydey

 

[Jonmo1]

You're welcome.

 

[barry houdini]

Also, something that I didn't mention, which might affect the output, I have use wildcards, i.e. "*Complete*", "*Pending*", etc. Would that affect the output at all? Are wildcards permissible while using the various nested functions?

No you can't use wildcards in the lookup array in MATCH function - best to stick with Jonmo's solution in that case