Countif

[LottieCampbell]

Hi,

I have a list of Inspections (various type, dates and locations etc) and I am trying to use a CountIFs formula to count the amount of Inspections that are either Type - 'Prelim' or 'Move Out' (this information comes from the same column called Type) and if the Tenancy Year field contains 1011.

The formula I am using is:
=COUNTIFS(AB1:AB2919,"1011****",K1:K2919,"Prelim",K1:K2919,"Move Out")

(AB = Tenancy Year column) (K = Type Column)

And I am just getting a zero count - which I know is wrong....

Any ideas on where I am going wrong here?

Thanks

[darknation144]

Incorrect infomation removed.

[LottieCampbell]

Thanks, But excel returns me an error for this, but doesn't specify what the problem is? Thanks

[darknation144]

Have you tried just using 1 star in your original formula. As * means multiple characters ? is used for a single character.

[LottieCampbell]

Well, the Tenancy Years appear in the following Format "1011BIRM" or "0910CARD" and I want it to count it where ever the Tenancy year contains 1011, which is why I have put 4 stars, to make the format match ... ? Is that right?

[darknation144]

Actually re reading your original post you are searching for Prelim AND Move Out you need to be searching for OR.

[darknation144]

You only need 1 * that will search for 1011 followed by anything.

[LottieCampbell]

ah thanks, will try that....

[darknation144]

Try =COUNTIFS(AB1:AB2919,"1011*",K1:K2919,"Prelim")+COUNTIFS(AB1:AB2919,"1011*",K1:K2919,"Move Out")