Need quick answer! (Excel)

Discussion in 'Windows Desktop Systems' started by Jae, Jun 17, 2002.

  1. Jae

    Jae Guest

    I have two worksheets.

    One is for the statistics analysis and the other one is filled with data.

    In the data worksheet (we'll call it ABC):
    You have the columns: A1 Birthdate, B1 Postal Code, C1 Sex
    In B1 Postal code, there is @#@ #@#. (6 Character)

    In C1 Sex. There are values of M or F down the list.


    Objective in the statistics analysis sheet: (We'll call it STAT)

    I have to analysis the number of male patients in a certain postal code. Example, in the postal code of M1B###, there could be 15 patients of which 7 are male and 8 are females.

    I need a formula, in which it can calculate the NUMBER OF MALE/FEMALE patients according the first 3 digits of the postal code.

    I've tried this:

    =COUNTIF(ABC!B:C,AND('Stat'!A2,ABC!C:C=M))

    Note: 'Stat'!A2 contains the value MIB* . So that retrieves all the MIB's in the ABC! column.

    But the problem is that, I can not state two conditions. Please help.

    Removing 'ABC!C:C=M' would make it count 15 which is correct if I wanted the number of patients. But I want the condition that out of those 15 patients, how many are male. (Hence, I added 'M'). But it does not seem to work.

    Anyone have a better solution? Thanks!

    - Jae
     
  2. koko

    koko Got Root?

    Messages:
    577
    Location:
    Columbia, S.C.
    bueller...bueller...

    :)
     
  3. greendevil

    greendevil Guest

    A quick & dirty solution is to add another column D, in which you for example state something like =IF(A1=$E$1;C1) where A is the column with postal codes, E1 is the box where you enter the data to check against (i.e. M1B*) and C is the column with gender.

    In that new column D you'll then get genders of all people with the right postal code, and FALSE/NULL for the rest. From there on it's a cakewalk to count the number of M/F.

    Well, it's one way of doing it...and it works.