Lookup Exact functions in formula?

[Roge]

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

 

[Teethless mama]

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

ctrl+shift+enter, not just enter

 

[ShaneDevenshire]

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.

 

[Roge]

Hi, thanks for the replies,

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" [rather than what you suggest
'subtract a "c" entry from the number of entries that contain "a", "d", "e"
or "abc"']

As "abc" name refers to a group name which includes the names "a","b" and
"c", so am trying to account for this, when I add up the number of specific
names. The anwer shouldnt be negative as there will only be a few "abc"
group names within my data.

In my example the answer=1, as 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.

This is seems quite complicated, not sure if the suggested formula is
matching the names I mention above or how to name the ranges where codes and
names are, thanks again for the help, Roge

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

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

 

[T. Valko]

I *think* this does what you want.

Array entered**:

=SUM(--(FREQUENCY(IF((COUNTIF(Code,Code)>1)*(ISNUMBER(SEARCH(Name,"abc"))),MATCH(Code,Code,0)),ROW(Code)-MIN(ROW(Code))+1)>0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP

Hi, thanks for the replies,

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" [rather than what you suggest
'subtract a "c" entry from the number of entries that contain "a", "d",
"e"
or "abc"']

As "abc" name refers to a group name which includes the names "a","b" and
"c", so am trying to account for this, when I add up the number of
specific
names. The anwer shouldnt be negative as there will only be a few "abc"
group names within my data.

In my example the answer=1, as 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.

This is seems quite complicated, not sure if the suggested formula is
matching the names I mention above or how to name the ranges where codes
and
names are, thanks again for the help, Roge

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

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