Hi,

Based on your example you want to subtract a "c" entry from the number of
entries that contain "a", "d", "e" or "abc" if they have the same code. Well
suppose that there are 5 entries that are either "a", "d", "e" or "abc" and
there are 6 enties with just "c" then you get a negative 1 if they all have
the same code. Is this what you really want? That is not what the suggested
formula will return, although I suspect that it may return what you really
want.

Also to use the supplied formula as written you must name the ranges where
your codes and names are.

--
Thanks,
Shane Devenshire


"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