COUNTIF formula with several variables in 1 column
 

Each day I want to be able to see how many boys and girls have participated
in various activities. In order to make as user friendly for my colleague as
possible I ask them to enter the data as follows in 1 column:

- G for girls
- S for Sports hall
- A for Art room
- M for Multipurpose area


The formula I used for the gender is =COUNTIF(H3:H510,"B"). I thought that
I could have GSM in a cell and still us that formula and that Excel would be
able to isolate each element.

Once more, it is not working.

I do not want to have to use countless columns. Any help would be grately
appreciated.

Thank you
--
NitaMax

COUNTIF formula with several variables in 1 column
 

Hi,

Try this:

=SUMPRODUCT(--(A1:A10={"B","S","M"}))

HTH
Jean-Guy

"NitaMax" wrote:

Each day I want to be able to see how many boys and girls have participated
in various activities. In order to make as user friendly for my colleague as
possible I ask them to enter the data as follows in 1 column:

- G for girls
- S for Sports hall
- A for Art room
- M for Multipurpose area


The formula I used for the gender is =COUNTIF(H3:H510,"B"). I thought that
I could have GSM in a cell and still us that formula and that Excel would be
able to isolate each element.

Once more, it is not working.

I do not want to have to use countless columns. Any help would be grately
appreciated.

Thank you
--
NitaMax

COUNTIF formula with several variables in 1 column
 

Try this:

A1 = G
A2 = S
A3 = M

=SUMPRODUCT(--(ISNUMBER(MATCH(H3:H510,A1:A3,0))))

Biff

"NitaMax" wrote in message
...
Each day I want to be able to see how many boys and girls have
participated
in various activities. In order to make as user friendly for my colleague
as
possible I ask them to enter the data as follows in 1 column:

- G for girls
- S for Sports hall
- A for Art room
- M for Multipurpose area


The formula I used for the gender is =COUNTIF(H3:H510,"B"). I thought
that
I could have GSM in a cell and still us that formula and that Excel would
be
able to isolate each element.

Once more, it is not working.

I do not want to have to use countless columns. Any help would be grately
appreciated.

Thank you
--
NitaMax

COUNTIF formula with several variables in 1 column
 

Just in case you happen to prefer using Countif(), try this:

=SUM(COUNTIF(H3:H510,{"G","S","M"}))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"NitaMax" wrote in message
...
Each day I want to be able to see how many boys and girls have
participated
in various activities. In order to make as user friendly for my colleague
as
possible I ask them to enter the data as follows in 1 column:

- G for girls
- S for Sports hall
- A for Art room
- M for Multipurpose area


The formula I used for the gender is =COUNTIF(H3:H510,"B"). I thought
that
I could have GSM in a cell and still us that formula and that Excel would
be
able to isolate each element.

Once more, it is not working.

I do not want to have to use countless columns. Any help would be grately
appreciated.

Thank you
--
NitaMax