Jun 13 2020 05:12 AM - edited Jun 13 2020 05:14 AM
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*"}))
Jun 13 2020 05:28 AM
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.
Jun 13 2020 05:58 AM - edited Jun 13 2020 06:24 AM
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
Jun 13 2020 06:23 AM
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))
Jun 13 2020 06:27 AM
Jun 13 2020 06:49 AM
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
Jun 13 2020 08:19 AM - edited Jun 13 2020 10:08 AM
@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*"}))