Count If with multiple criteria and unique values

1
votes

I have a sheet of data and want to run a count on the values in Raw data with the following criteria:

  • If the value in raw data is repeated FOR EXAMPLE London No1 and London No1 AUTO only to be counted once, but if the value in raw data is London No1, London No2 and London No1 Auto then only to count 2 (to count London No1 and London No2 only).

    Raw data

     London No1 
 London No2 
 London No1 Auto
 London No2 Auto 
 Bristol No1 Auto
 Cardiff No1
 Cardiff No2

In this example Result should be: London - 2, Bristol - 1, Cardiff - 2.

http://i.stack.imgur.com/E7oco.jpg

2
excelexcel-formula
This question is not clear at all. What does your raw data look like, and what would you like your result to look like? – Grade 'Eh' Bacon
Sorry i thought it might be confusing. So in raw data I got London NO1 and London No1 Auto, then i got Bristol NO1 Auto. I need to count how many London there are, however if there is NO1 and NO1 Auto i need to tell me that there is only 1, but when there is Bristol NO1 Auto i need to count that and tell me that Bristol has 1 too. Does this make sense? – Dovile
Please upload a picture of your data or fill in a table which shows a broader example of what you mean. And please take a look at re-writing your paragraph in your original post; some of it I can't parse at all. – Grade 'Eh' Bacon
I have edited the original query and uploaded the photo. I don't think i can explain what i am trying to do any better, hope this make sense now. – Dovile
@Dovile What's the status. Have you tried any of the answers provided? – EEM

2 Answers

1
votes

This solution uses only one "working column" and assumes the following:

  • The list of Raw Data is located at A1:A16 (adjust as required)
  • The Raw Data records follow this syntax City NoX string all separated by a blank space. X is one digit number (do let me know if there will be larger numbers in order to adjust the formula)

(All ranges include header)

With the list of results located in the same worksheet at B1:D16 (adjust as required) and including the following fields:

Site: List of Cities to count in Raw Data

Unique Record: Used to list unique "clean" city records before counting. Enter this FormulaArray in D2 and copy till last record

(Formulas Array are entered by pressing [Ctrl] + [Shift] + [Enter] simultaneously)

=IFERROR( INDEX(
MID( $A$2:$A$16, 1, 2 + FIND( "No", $A$2:$A$16 )),
MATCH( 0, COUNTIF( D$1:D1,
MID( $A$2:$A$16, 1, 2 + FIND( "No", $A$2:$A$16 ))), 0 ) * 1 ), "" )

Result Count: Enter this FormulaArray in C1 and copy till last record

=IF( EXACT( $B2, "" ), "",
IFERROR( COUNTIF( $D$2:$D$16, $B2&"*" ), "" ))

enter image description here

0
votes

Assumptions

So if I understand your clarifications, you have a list of cities, and each city might have a different "No" on it. ie: some will be "No1", "No2", etc. Also, some might have "Auto" on it. There are sometimes duplicates, ie: "London No1" might appear twice. Also, whether or not the word "Auto" appears, it will be a duplicate if that "No" already appears. ie: "London No1" is identical to "London No1 Auto".

Helper Columns

To count how many unique instances of each city there are, I propose you use a helper column which first removes the word auto, and then only displays itself if it is not a duplicate of a prior entry.

With your data in column A, starting at A2, enter this formula in B2 and copy down:

=IF(ISERROR(MATCH(TRIM(SUBSTITUTE(A2,"Auto","")),$B$1:$B1,0)),TRIM(SUBSTITUTE(A2,"Auto","")),"")

This formula works as follows:

-First it takes the text in A2, and it replaces the word "Auto" with "". Then it TRIMS the resulting text, which means it earases any trailing spaces at the end. It takes that trimmed text, and attempts to MATCH it with the previous rows in column B. If it doesn't find a match, it results in an error. IF it ISERROR, then it shows the text (because it's the first time it has appeared). Otherwise, it shows "".

Array Formula

Now that you have this in column B, we will use Array Formulas to count how many unique instances of each city appeared. An Array Formula is a specific formula which calculates many time over an entire range, instead of just one cell. It then returns an Array of results, which we need to collapse into a single answer. For example: 

=SUM(IF(A1:A5>5,B1:B5,""))

[Confirmed with CTRL + SHIFT + ENTER instead of just ENTER]

In the above example, the Array Formula will check if each individual cell from A1:A5 is a number larger than 5. If it is, it will return the cell from column B in that row. It then takes all resulting answers from column B (where A was > 5), and SUMS them to a single number.

In this case, the Array Formula will be as follows [Starting in D5 in your image and moved one column to the right, immediately to the right of the first city name, and copied down for each city; I am assuming that your raw data goes from A2:A100]:

=SUM(--NOT(ISERROR(SEARCH(C5,$B$2:$B$100))))

Confirm this formula with CTRL + SHIFT + ENTER instead of just ENTER. Then copy down for each city name in column C.

It works by attempting to search each unique value in column B for the city name shown in that row of column C. Wherever it the city name is not present, ISERROR returns TRUE for that element of column B. Then NOT turns all TRUEs to FALSEs and vice versa. Then the -- tells excel to convert TRUE and FALSE to their numerical equivalents [1 and 0, respectively]. So we will have a TRUE [AKA 1] for each match, and FALSE [AKA 0] for everything else. The sum of all 1's is the number of matches of that city name in your list of unique entries.