# Counting the number of differing values across a group of cells

1. ## Counting the number of differing values across a group of cells

hi, can anyone help me with a problem that has me stumped?
i am trying to calculate the number of times a different combination of 2 cells appears across a range of cells - please see sample below:

A B C D E F G H I J K L
E E E E F F F F E E F F

So for the combination of the value in Cell A+B - I need to count the number of different values across A+B and E+F and I+J - so in this case it would be 2, as we ignore cells C,D,G,H,K,L etc.

Does this make sense? The cells are basically languages that I am trying to calculate the amount of localisation and translation work required - so the values per cell can have up to 18 differing combinations per cell.

Thanks

James

2. Hello James,

Welcome to the forum.

With your letters in A:L, put this in A2 and copy across to L2

=CODE(A1), then in A3

=IF(A2=B2,A2,"") copy formula to E3 and I3, then in M3 put this

=SUMPRODUCT((1/COUNTIF(A3:L3,A3:L3&"")))-1

Does that work for you?

3. Thanks OldChippy

I am still lost, but am convinced it is becuase i didnt explain it... I have attached an extrace of the file with comments on - can you possibly look at it and let me know if there is a forumla i can use for this?

4. Just explain from your first comment box,

number of differing cells contained in I4 and J4 do you mean number of differing characters in each cell?

5. Thank you for keeping on trying... what am i like??

i dont want to count what is in each cell, but I want to count the combination of the 2 cells vs the combination of the next 2 cells that are positioned 4 cells to the right etc across the row.

So I4 with J4 make up 1 product, and that product is for the UK, but our French teams may also use the same product - therefore the I4 with J4 would be the same as M4 with N4, but then Germany may take a different product, so although Q4 may be the same as M4 and I4, R4 may be different. So that is why it is important to look at the 2 cells together as making up 1 whole product (I4+J4) (M4+N4) (Q4+R4) repeating every block of 4 till the end of the row.

I think i have totally confused everyone readingt this now

Thank you...

6. There may be a better way to do this, but does this help?

7. THANK YOU!!!!

Saved me endless hours of adding up silly cells... more time in the pub and watching the rugby...

Thanks again

8. Well thats got to be worth it then - thanks for the feedback

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1