Need quick answer! (Excel)

[Jae]

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

 

[koko]

bueller...bueller...

 

[greendevil]

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.