Multiple Variable Countif?

**motobeyek** · 09-04-2010, 11:50 AM

I am looking for a formula that will look at text in multiple columns, compare, and give a total number of unique "locations". Attached is a small list showing how each cell can have variables.

A challenge for my application is that I won't be able to sort, which I think would make it a lot easier. I need a tool that evaluates the entire spreadsheet as is. Is it possible?

Thanks!

**zbor** · 09-04-2010, 12:06 PM

7? By which column?

I assume column C and get 5:

=SUM(IF(FREQUENCY(MATCH(C3:C16, C3:C16, 0), MATCH(C3:C16, C3:C16, 0))>0, 1))

**JBeaucaire** · 09-04-2010, 12:07 PM

After you add a "key" column to concatenate all the values of your addresses into a single cell for each row, this standard "UNIQUE TOTAL" formula works:

=SUMPRODUCT((G2:G16<>"") / COUNTIF(G2:G16, G2:G16 & ""))

**DonkeyOte** · 09-04-2010, 12:12 PM

Is the answer not 6 ?

If preferred you can avoid the concatenation and use:

=SUMPRODUCT(--(C3:C16<>""),--(MATCH(C3:C16&"@"&D3:D16&"@"&E3:E16&"@"&F3:F16,C3:C16&"@"&D3:D16&"@"&E3:E$16&"@"&F3:F16,0)=(ROW(C3:C16)-ROW(C3)+1)))

Not saying you should though... concatenation offers many advantages and if you use helpers I would suggest extending such that SUMPRODUCT/Array is circumvented altogether.

**motobeyek** · 09-04-2010, 12:26 PM

Thanks, for the input everyone. I've attached a second file showing a sort, and how I came up with 7.

Amy is a unique location, Doug actually has 2 locations because he has 2 different addresses, etc..so one can see how one change in one cell can result in a "unique location".

I will try some of the formulas you have suggested, but maybe this new info requires a new approach?

**zbor** · 09-04-2010, 12:29 PM

Then you need just to join ranges (You can add more columns by & if you like only keep same principle)...

=SUM(IF(FREQUENCY(MATCH(A23:A36&C23:C36, A23:A36&C23:C36, 0), MATCH(A23:A36&C23:C36,A23:A36&C23:C36, 0))>0, 1))

Edit:

SUM(IF(FREQUENCY(MATCH(A23:A36&C23:C36&E23:E36, A23:A36&C23:C36&E23:E36, 0), MATCH(A23:A36&C23:C36&E23:E36,A23:A36&C23:C36&E23:E36, 0))>0, 1))

Check also other approaches.

**DonkeyOte** · 09-04-2010, 12:29 PM

I don't think many people would automatically assume Name as being relevant to a "Location" in a dataset that includes columns: Address, Suite, City & State

Assuming all columns are relevant and not just C:F adjust the approaches you've been given accordingly - the last SUMPRODUCT ex. would become:

=SUMPRODUCT(--(A3:A16<>""),--(MATCH(A3:A16&"@"&B3:B16&"@"&C3:C16&"@"&D3:D16&"@"&E3:E16&"@"&F3:F16,A3:A16&"@"&B3:B16&"@"&C3:C16&"@"&D3:D16&"@"&E3:E$16&"@"&F3:F16,0)=(ROW(C3:C16)-ROW(C3)+1)))