=SUM(N(FREQUENCY(IF(ISNUMBER(MATCH(Name,{"abc","d" ,"e","a"},)),MATCH(Code,Code,)),MATCH(Code,Code,)) 0))

ctrl+shift+enter, not just enter


"Roge" wrote:

Hi, am trying to work out a formula, would really appreciate some help though -
a simplified example of my data -

A B
code name
1 MG1 abc
2 MG2 d
3 MG3 e
4 MG1 f
5 MG5 g
6 MG6 h
7 MG1 a
8 MG8 j
9 MG9 abc
10 MG10 a

where "abc" name refers to a group which includes the names "a","b" and "c"
I have a COUNTIF formula for counting the number of times certain names, say
"abc","d","e" and "a", occur [=5 above]

I would like to subtract the number of times "a","b" or "c" occur when their
code is the same as the code for any "abc", as they would already be
represented by that name, answer=1 above [row 1 "abc" and row 7 "a" both
contain "a" and have the same code, whereas row 10 "a" has a different code
so doesnt need to be subtracted]

I can't work out how to write a formula for this subtraction, maybe it is
too difficult. I think the formula needs to search for specific names[values]
and lookup exact matching codes for those names. Any help would be great, Roge