Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Data in two seperate Spreadsheets | Excel Worksheet Functions | |||
how can i multiply two columns | Excel Worksheet Functions | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions |