Challenging SUMING situation!

[Guntars]

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

 

[JBeaucaire]

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,"x")+COUNTIF(H:H,"x")

 

[Herbert Seidenberg]

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

 

[T. Valko]

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.

 

[T. Valko]

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(COLUMN(B1:F1))^0)>0))

 

[Guntars]

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.

 

[T. Valko]

You're welcome. Thanks for the feedback!