Excel: Countifs "Not equal to"

1
votes

I'm having trouble writing a Countifs to accomplish what I want. I run a claims report every month with tons of data organized in columns and export it to excel every month. On a separate tab I have formulas that reference the tab that this data is copied to.

The formulas are used to count and organize the claims according to certain criteria. One of the columns (G:G) is "State of Jurisdiction".

The state of jurisdiction for each claim is important because certain groups of "special states" get charged a unique rate, and the "non special states" get charged a flat rate.

I've figured out how to count the "special states" without issue. my problem is counting the "non special states". I need to be able to write a countifs that references a range, for example, 010:U10, and count everything in column G EXCEPT what's stored in range O10:U10. I need 010:U10 to be able to have blank cells for expansion. Let me know if any additional information is necessary.

3
excelexcel-formula
you have a total of 7 exceptions - even if they triple over time - you have multiple ways of excluding them in whatever way you want to do your checks. its fairly easy if you can guarantee your special states cells will always have values. – helena4

3 Answers

2
votes

This could be done with Array Formulas in a single cell, but as you seem less comfortable with Excel, then I would recommend that you use some helper columns.

So instead of just reading directly from row 10 in O->U and trying to search all of them within your COUNTIFS formula, add a new row column to your data. This new column will indicate solely whether the jurisdiction in column G is a special state. So for example, assuming that $O$10:$U$10 has a list of all special states [I'm not clear on what sheet everything is on here, so you may need to adjust]. In H2 and copied down for all of your data entries, put the following [This assumes your data starts on G2]:

=IF(ISERROR(MATCH(G2,$O$10:$U$10,0)),"Non-Special State","Special State")

This says: try to MATCH the jurisdiction name from G2, out of the list of all special states in row 10. If there's no match, that means that it's not a special state, and MATCH will return an error. ISERROR will then return TRUE, which will make the IF statement give the result "Non-Special State". If MATCH finds a result, however, it will return "Special State".

Then, in your countifs formula, add this criteria:

=COUNTIFS(SUM COLUMN, OTHER CRITERIA COLUMN, OTHER CRITERIA, H:H, "Non-Special State")
2
votes

The way to do exactly what you're asking for, though I'd advice against it, would be something like

=COUNT(A2:A21)-COUNTIF(A2:A21,C1)-COUNTIF(A2:A21,C2)

So on for each cell you want to include, long and tiresome.

But as you say you can count the special, just do "total" minus "special total"

0
votes

Any formula that single-handedly performs this will be using cyclic calculation (with or without Ctrl+Shift+Enter or CSE) so you are going to want to cut the range of cells in column G down to what is absolutely necessary. Full column references will result in many blank cells being calculated that do not have to be.

As a standard non-CSE formula with full column reference with G:G,

=SUM(INDEX(SIGN(LEN(G:G))*ISERROR(MATCH(G:G, O10:U10, 0)), , ))

With dynamic range references,

=SUM(INDEX(SIGN(LEN(G2:INDEX(G:G, MATCH("zzz", G:G ))))*ISERROR(MATCH(G2:INDEX(G:G, MATCH("zzz", G:G)), O10:U10, 0)), , ))

      sum_countif34

The formula assumes that column G contains text, not numbers.