count formula, blanks and multiple text strings

[Dhogstol]

hi, i need to make a formula too count some cells.

1st criteria is that cells in column T shall be blank. (non blank cells contains date and time)
2nd criteria, column L the cells to include in count shall have the text BRC or FSA or both . How do i define this in a formula to not get duplicate counts ?

here is the formula i have been trying to use

=SUM(COUNTIFS(T2:T10000,"",L2:L10000,{"*BRC*";"*FSA*"}))

[#2]

@Dhogstol 

• Your column L is bothersome if only from a data integrity point of view. If it can contain "the text BRC or FSA or both" what else can it contain that we want to make sure we're NOT including in the account?
• Is this a table that you created--in which case we might be able to clean it up--or is it one that you're accessing but somebody else is responsible for the way it's laid out?

Is it possible to upload a sample of the actual file (having first removed any confidential information that might be in other columns)? It's a lot easier to test with the actual than to create our own from your description, since the actual (as my question above suggests) might contain a variety of challenges.

[#3]

@mathetes 

I have recreated the scenario in the attached excel file. as the original file holds sensitive data.
I really appreciate your help - i have been trying to figure this out for the 2days now.

The Column L, is a description field where multiple users have made input to the different items(rows), it is a plain text field. with descriptions of issues, classification codes and dates and random numbers.

This is a list generated by client system, the list layout will stay consistent. in this case it is used as basis for a statistics report. i usually use 4-5 hours to extract the data i need by using the filter functions in excel and the table. so im trying to automate this process. and im left with this last formula which causing me a headache

[#4]

@Dhogstol 

With COUNTIFS you count not number of cells, but how many times one or another text appears in cell.

As variant formula could be

=SUMPRODUCT((B2:B9973="")*((ISNUMBER(SEARCH("FSA",A2:A9973))+ISNUMBER(SEARCH("BRC",A2:A9973)))>0))

[#5]

Thank you so much Sergei, this made my day. Thanks for your interest in the issue as well @mathetes

[#6]

@Dhogstol 

It's great that @Sergei Baklan was able to give you a formula that works with your example. Really.

And my guess is that it'll work reliably 99% of the time.

I still (coming from an IT background with a major database of a major corp) would worry that there'll be those times when that column, which looks like it collects all kinds of stuff, will just happen to have a string of text (perhaps a misspelling) that looks like BRC or FSA but isn't.

If this is a mission critical extraction that you're doing, I highly recommend that you see if it's at all possible to address that underlying issue of data integrity. It's really not good practice to have a column of cells that contain garbage; recall the old saw, GIGO, "Garbage in, garbage out"

It should be relatively easy for whoever it is who is responsible for the basic design and input to create a couple of clean columns (or maybe more) that are exclusively for BRC and FSA, using data validation to insure nobody enters it with a trailing or leading space, etc.

If it's not mission critical, then leave it....but take your results with a grain of salt

[#7]

@Dhogstol , or continuing your thought process, you may visualise a venn diagram and subtract the intersection (i.e. the duplicates which contain both BRC and FSA in 2 possible orders)

=SUM(COUNTIFS(B2:B9973,"",A2:A9973,{"*BRC*";"*FSA*"}))-SUM(COUNTIFS(B2:B9973,"",A2:A9973,{"*BRC*FSA*","*FSA*BRC*"}))