Formula to filter COUNTIFS for discrete values too

[sledgehama]

Hi team!

Excel not-quite-newbie-but-clearly-not-skilled-enough here so any help would be great!

I’m trying create a formula that’s going to count the total number of text-filled cells that *do not* contain either of one of two certain texts, also does not count entirely blank cells, and filtered for discrete values on a different column and also dependent on there being one of several values in *another* column. It’s possibly overlong and complicated a situation as it is, but I’m hoping to be able to just dump raw data into the worksheet and have the reporting formula get what I need from it without having to do anything else to it. I’ve managed to get so far, but the trick really seems to be combining them all and I keep running into problems there!


The relevant columns are:

A: Record ID

D: Record Status

I: Surname

My logic is that if we count column D for the Record Statuses we want (A, C & F), then count the cells in the Surname that don’t have either of the two terms we want to exclude (“- left” or “- decea”) and also don’t count those that are blank then the only thing left is to be able to filter them by discrete values of the Record ID. To get to this point I’ve got the formula:

=SUM(COUNTIFS($D$2:$D$200000,{"A","C","F"},$I$2:$I$200000,{"<>*- decea*"},$I$2:$I$200000,{"<>*- left*"},$I$2:$I$200000,"<>"&""))

Which *should* be all the rows that are on the specified statuses, don’t have ‘-deceas’ or ‘- left’ or just a blank in the Surname column. Am I right on that last aspect for *not* counting the blank cells? The resulting sum looks right but I’m a little bit unsure as to if this is telling it to count ones that *aren’t* blank or count them if they *are* blank and that I'm getting the and/or right to the logic there.

From there I need to be able to filter them by discrete values of column A, but I just can’t figure out how to implement that in there without breaking the whole formula. Help?

 

[keda duck]

=SUM(COUNTIFS($D$2:$D$200000,{"A","C","F"},$I$2:$I$200000,{"<>*- decea*";"<>*- left*"},$I$2:$I$200000,"<>"&""))

 

[RoryA]

Since you have 365 I think you'd be better off using the FILTER function to return the values in column A based on your criteria, then wrap that in COUNTA(UNIQUE(...))

 

[RoryA]

{"<>*- decea*";"<>*- left*"}

That would work as an OR and it needs to be an AND

 

[sledgehama]

Since you have 365 I think you'd be better off using the FILTER function to return the values in column A based on your criteria, then wrap that in COUNTA(UNIQUE(...))

Okay, so I've got:

=COUNTA(UNIQUE(FILTER($A$2:$A$200000,(($D$2:$D$200000="A")+($D$2:$D$200000="C")+($D$2:$D$200000="F"))),"No results")))

and that comes out with the right figures, but trying to add in the "<>*- decea*" or "<>*- left*" text filters is eluding me. I tried to do it as:

=COUNTA(UNIQUE(FILTER($A$2:$A$200000,(($D$2:$D$200000="A")+($D$2:$D$200000="C")+($D$2:$D$200000="F"))*(($I$2:$I$200000="<>*- left")+($I$2:$I$200000="<>*- decea")),"No results")))

but that clearly wasn't giving the right answer since it was just giving 1 and removing the 'COUNTA(UNIQUE' just had it coming up as 'No results'. Help?

 

[RoryA]

You need to multiply, not add, in the criteria for column I since both things need to be true.

 

[sledgehama]

You need to multiply, not add, in the criteria for column I since both things need to be true.

So:
=COUNTA(UNIQUE(FILTER($A$2:$A$200000,(($D$2:$D$200000="A")+($D$2:$D$200000="C")+($D$2:$D$200000="F"))*(($I$2:$I$200000="<>*- left")*($I$2:$I$200000="<>*- decea")),"No results")))

That still doesn't seem to work and is only returning 1 ('No Results' when taking it just to the Filter level). Do filter formula not accept wildcards for text? I tried constructing a filter for just "<>*- left" and it didn't seem to work either. It only worked when there was an exact match on the entire cell contents which doesn't help me since the text it needs to match is always going to be part of the cell contents, not the entirety of it.

Sorry to be such a pain!

 

[RoryA]

Not quite:

=COUNTA(UNIQUE(FILTER($A$2:$A$200000,(($D$2:$D$200000="A")+($D$2:$D$200000="C")+($D$2:$D$200000="F"))*((right($I$2:$I$200000,6)<>"- left")*(right($I$2:$I$200000,7)<>"- decea")),"No results")))

 

[sledgehama]

Ah, okay. If I'm wanting to use that same forumal to serach for including text rather than excluding it, what do I need to change? I tried just removing the '<>' and that didn't work.

Thanks for all your help!

 

[RoryA]

replace the <> with =