In my opinion this is quit advanced stuff I am trying to do here. Thats why
I am posting here were all the excel experts meet.
This is my challenge: in Col. A2:A11 I have names of workers. On top of the
table in row B2:I2 I have vacation names where employees been traveling B2
(Asia), B3 (Europe), B4 (America), B5 (Australia), B6 (Asia), B7 (Europe), B8
(America), B9 (Australia). As you can see continent names are duplicated in
B2:I2 range of cells, thats because it was I different trip.
What I want is to sum in one cell, how many employees have been to Europe.
From what I understand this is calling for array formula.
Thank you very much.
Here is replicate of excel table:
As Eu Am Au As Eu Am
Name 1 x X X
Name 2 X
Name 3 X X
Name 4 X X
Name 5
Name 6 X X X
Name 7 X
Name 8 X X X
Name 9 X
Name 10 X

No array is necessary, these are simple COUNTIF formlas:

To get As total:
=COUNTIF(B:B,"x")+COUNTIF(F:F,"x")

To get Eu total:
=COUNTIF(C:C,"x")+COUNTIF(G:G,"x")

To get Am total:
=COUNTIF(D:D,"x")+COUNTIF(H:H,"x")
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Guntars" wrote:

In my opinion this is quit advanced stuff I am trying to do here. Thats why
I am posting here were all the excel experts meet.
This is my challenge: in Col. A2:A11 I have names of workers. On top of the
table in row B2:I2 I have vacation names where employees been traveling B2
(Asia), B3 (Europe), B4 (America), B5 (Australia), B6 (Asia), B7 (Europe), B8
(America), B9 (Australia). As you can see continent names are duplicated in
B2:I2 range of cells, thats because it was I different trip.
What I want is to sum in one cell, how many employees have been to Europe.
From what I understand this is calling for array formula.
Thank you very much.
Here is replicate of excel table:
As Eu Am Au As Eu Am
Name 1 x X X
Name 2 X
Name 3 X X
Name 4 X X
Name 5
Name 6 X X X
Name 7 X
Name 8 X X X
Name 9 X
Name 10 X

Excel 2007 PivotTable
Most advanced stuff used:
=SUM(INDIRECT(Cntry))
http://www.mediafire.com/file/gguwd2wfn2m/04_26_09.xlsx

I think your description of where your column headers are located is
misleading.

Let's assume your column headers are in the range B1:I1. Names in the range
A2:A11.

This array formula** will return the count of *unique* names that have a "X"
in any column corresponding to Europe.

=SUM(--(FREQUENCY(IF((B2:I11="x")*(B1:I1="Europe"),ROW(B2 :I11)),ROW(B2:I11))0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

For example:

...........A..........B..........C..........D
1................Europe....Asia...Europe
2...Name1.....X.......................X
3...Name2..................X..........X

The above formula will return 2.


--
Biff
Microsoft Excel MVP


"Guntars" wrote in message
...
In my opinion this is quit advanced stuff I am trying to do here. That's
why
I am posting here were all the excel experts meet.
This is my challenge: in Col. A2:A11 I have names of workers. On top of
the
table in row B2:I2 I have vacation names where employees been traveling B2
(Asia), B3 (Europe), B4 (America), B5 (Australia), B6 (Asia), B7 (Europe),
B8
(America), B9 (Australia). As you can see continent names are duplicated
in
B2:I2 range of cells, that's because it was I different trip.
What I want is to sum in one cell, how many employees have been to Europe.
From what I understand this is calling for array formula.
Thank you very much.
Here is replicate of excel table:
As Eu Am Au As Eu Am
Name 1 x X X
Name 2 X
Name 3 X X
Name 4 X X
Name 5
Name 6 X X X
Name 7 X
Name 8 X X X
Name 9 X
Name 10 X

Here's another way. Normally entered.

=SUMPRODUCT(--(MMULT((B2:I11="x")*(B1:I1="Europe"),{1;1;1;1;1;1; 1;1})0))

If you have more than a "few" columns of data then this array entered
version:

=SUM(--(MMULT((B2:F6="x")*(B1:F1="Europe"),TRANSPOSE(COLU MN(B1:F1))^0)0))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
I think your description of where your column headers are located is
misleading.

Let's assume your column headers are in the range B1:I1. Names in the
range A2:A11.

This array formula** will return the count of *unique* names that have a
"X" in any column corresponding to Europe.

=SUM(--(FREQUENCY(IF((B2:I11="x")*(B1:I1="Europe"),ROW(B2 :I11)),ROW(B2:I11))0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT key then hit ENTER.

For example:

..........A..........B..........C..........D
1................Europe....Asia...Europe
2...Name1.....X.......................X
3...Name2..................X..........X

The above formula will return 2.


--
Biff
Microsoft Excel MVP


"Guntars" wrote in message
...
In my opinion this is quit advanced stuff I am trying to do here. That's
why
I am posting here were all the excel experts meet.
This is my challenge: in Col. A2:A11 I have names of workers. On top of
the
table in row B2:I2 I have vacation names where employees been traveling
B2
(Asia), B3 (Europe), B4 (America), B5 (Australia), B6 (Asia), B7
(Europe), B8
(America), B9 (Australia). As you can see continent names are duplicated
in
B2:I2 range of cells, that's because it was I different trip.
What I want is to sum in one cell, how many employees have been to
Europe.
From what I understand this is calling for array formula.
Thank you very much.
Here is replicate of excel table:
As Eu Am Au As Eu Am
Name 1 x X X
Name 2 X
Name 3 X X
Name 4 X X
Name 5
Name 6 X X X
Name 7 X
Name 8 X X X
Name 9 X
Name 10 X

Gentlemen,
Thank you very much, for replies. The array formulas created by T. Valko did
the job. Really, array formulas do magic.
Next time I will try to be better on explaining my challenges.
Again, thank you all, for good suggestions and solutions.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Guntars" wrote in message
...

Gentlemen,
Thank you very much, for replies. The array formulas created by T. Valko
did
the job. Really, array formulas do magic.
Next time I will try to be better on explaining my challenges.
Again, thank you all, for good suggestions and solutions.