negative countifs

[Andy83UK]

I am trying to use a formula to count a large number of occurences on a spreadsheet subtracting away a couple of variables, so far I haven't been able to acheive this on a test sheet.

The formula I'm trying to make work is

=COUNTA((B2:B14))-COUNTIFS(B2:B14,"e",B2:B14,"f")

I think that is basically doing what I require as I want to toal up a much larger sheet of a couple of thousand rows of info minusing away any with the criteria of e and f.

Thanks
For your help.

 

[RoryA]

Try:
=COUNTA(B2:B14)-COUNTIF(B2:B14,"e")-COUNTIF(B2:B14,"f")

 

[Andy83UK]

Sorted!

Thanks very much for your help, knew it would be something simple i'd done wrong!

Thanks

 

[RoryA]

You could also use:
=COUNTA(B2:B14)-SUM(COUNTIF(B2:B14,{"e","f"}))
if you wanted.

 

[Andy83UK]

thanks that second one may be simpler in longer formulas of which this will likely be a part i think.

 

[Andy83UK]

Is there a way to have this formula work with an if criteria before it?

such as if $M:$M = Dee then perform this action?

 

[RoryA]

I think you'd need SUMPRODUCT for that. Do you mean count where M is "Dee" and B is not "e" or "f" (can B be blank?)?

 

[Andy83UK]

i am trying

=IF($A:$A=A6,"")*(COUNTA(B2:B14)-COUNTIF(B2:B14,"e")-COUNTIF(B2:B14,"f"))

where A6 = Dee

 

[Andy83UK]

Yes B could possibly be blank but shouldn't M could be a variety of possibilities including a lot of blanks but I would change the formula's entry for M's criteria as needed.

the formula where i've been using Columns A and B is just on a blank test sheet.

 

[RoryA]

Something like:
=SUMPRODUCT(($A$2:$A$10="Dee")*ISNA(MATCH($B$2:$B$10,{"e","f"},0))*($B$2:$B$10<>""))