You're welcome!

Biff

"ExcelMonkey" wrote in message
...
Slick! I was on the right track assuming a countif type function instead
of
a Match. Just could not get it to work. Thanks a million. I really
appreciate it. Been plaguing me for a few days. I can sleep now!

EM

"T. Valko" wrote:

Take a look at this screencap:

http://img477.imageshack.us/img477/1061/sampleja9.jpg

Biff

"ExcelMonkey" wrote in message
...
Below, I have annual data ("profiles") in range B2:D6. I use the names
in
range A2:A6 to drive data validation dropdown boxes in the range
B9:D11.

My goal is to use this data to populate various business units of a
firm
("sites") across various cost centres ("Var Cost"). In range B14:D16 I
want
to show the consolidated "Site" data expressed across time for each
"Var
Cost".

The solution you provided works fine if I do not have duplicates.
Using
=SUMPRODUCT(--(ISNUMBER(MATCH($A$2:$A$6,$B9:$D9,0))),B$2:B$6) in cell
B14,
I
get the sum of Profiles1,2 and 3 for 2006. However this does not work
in
B15
as =SUMPRODUCT(--(ISNUMBER(MATCH($A$2:$A$6,$B10:$D10,0))),B$2:B$6) has
a
duplicate in it. The result does not take into account the duplicate
"Profile 2".

I was hoping that I could come up with a way to use the sumproduct
formual
so that I could create an array which showed the number of times the
chosen
profiles (in data validation boxes) per Var Cost showed up in the
original
list of "Profiles" (B2:B6).

{1,1,1,0,0} for B14
{0,2,1,0,0} for B15
{0,0,1,1,1} for B16

Not I am doing this because I do not want to use pivot tables nor VBA.
It
needs to be formulas. And I want to consolidate the "Site" data. Any
easier
way around this would be to not consolidate the data and break "Var
Cost"
down by site. But I do not want to do this.

Is there a way to compare two ranges where you can create an array
illustrating the number of times the items in the first array appear in
the
second array? If not, do you have any other ideas?


A B C D
1 2006 2007 2008
2 Profile 1 0.50 0.65 0.60
3 Profile 2 0.68 0.17 0.37
4 Profile 3 0.51 0.42 0.94
5 Profile 4 0.50 0.08 0.07
6 Profile 5 0.87 0.14 0.19

7 Site 1 Site 2 Site 3
8
9 Var Cost 1 Profile 1 Profile 2 Profile 3
10 Var Cost 2 Profile 2 Profile 3 Profile 2
11 Var Cost 5 Profile 5 Profile 4 Profile 3
12
13 2006 2007 2008
14 Var Cost 1 1.696 ? ?
15 Var Cost 2 1.193 ?
16 Var Cost 3 ? ?

Thanks for your patience.

EM


"T. Valko" wrote:

Why don't you tell us what you are attempting to do with an example
*and*
the expected result?

Biff

"ExcelMonkey" wrote in message
...
Yes this works but I just realized that this does not work they way
I
need
it
too for duplicates. So if my example has a duplicate in Range 2
(i.e.
"Rain")
the result will look like {1,0,0,0,0,1,0,0,0,0} - as it should.
But I
would
need it to look like this {1,0,0,0,0,2,0,0,0,0} which is no longer
boolean
as
I originally suggested. How would I incorporate this to accomodate
the
duplicate? Remember I want to put it into a SUMPRODUCT function.
SUMPRODUCT({1,0,0,0,0,2,0,0,0,0}, OtherRange). I need to
incorporate a
countif into the array.

Thanks

EM

Assume Range 1 is as follows (A1:A10):
Dog
Cat
Man
Women
Car
Rain
Black
In
Peter
Acid

Assume Range 2 is as follows (A12:A16):
Rain
Blue
Rain
Stick
Dog

"T. Valko" wrote:

This will evaluate to that array:

--(ISNUMBER(MATCH(A1:A10,A12:A16,0)))

What are you trying to do?

Biff

"ExcelMonkey" wrote in
message
...
Apologies for the re-post. Need to clarify this to avoid
confusion.
Is
is
possible to compare two ranges and return a list of booleans
illustrating
where the values in one range reside in the other.

Assume Range 1 is as follows (A1:A10):
Dog
Cat
Man
Women
Car
Rain
Black
In
Peter
Acid

Assume Range 2 is as follows (A12:A16):
Rain
Blue
Mary
Stick
Dog

In cell A17, I want to insert a SUMPRODUCT function. For the
first
range
variable of the SUMPRODUCT, I want to illustrate where the items
in
Range
2
reside in Range 1 as follows. {1,0,0,0,0,1,0,0,0,0} showing that
both
"Rain"
and "Dog" reside in Range 1 in positions 1 and 6. The the first
range
in
the
SUMPRODUCT function would look like this if you highlighted in
and
hit
F9:

SUMPRODUCT({1,0,0,0,0,1,0,0,0,0}, Range2)

Any ideas on what type of logic I would use within this
SUMPRODUCT
function
to create the boolean data above?

Thanks

EM