Help with searching in EXCEL

[SteveLord]

I just started working for the Board of Medicine and I need to find out how to count these doctors better.

We have over 48,000 total, but that includes dead guys all the way back 100 years or something.

Anyway, I need to count how many specialize in Psychiatry, but are also ACTIVE. Now, in EXCEl there is a column for each of these. Each specialization has a number and another column will have a 3 if that person is still active.

How can I count how many serve a certain specialty and also find out if they are active?

Thanks. Excel newb here.


----------
IT Admin
Iowa Board of Medicine

 

[SkipVought]

Hi,

Check out the AutoFilter. You can set criteria on multiple columns.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SteveLord]

Hope I was specific enough. If not, here's another way to ask it.

Like, how do I search for how many rows contain a value of 60 and then a few columns over, have a value of 3? How can I only get results that meet that?




----------
IT Admin
Iowa Board of Medicine

 

[SkipVought]

There are all kinds of ways to skin a cat.

1) AutoFilter:
a) Data > Filter > AutoFilter
b) Set first column criteria to 60
c) Set second column criteria to 3
d) Select ONE COLUMN within the table
e) Right-Click in the Status Bar (bottom edge) and select COUNT.

2) Use Named Ranges:
a) Select ALL data in your table, including the headings
b) Insert > Name > Create - Crete names in TOP row.
c) =SUMPRODUCT(--(YourFirstColumn=60)*(YourSecondColumn=3))

3) PivotTable: Use the PivotTable wizard to report on MANY summary values within your table.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

More specifically with the PivotTable, drag the two field headings in question into the PAGE area and use the Count of Doctor Name. Then select 60 and 3 respectively.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SteveLord]

Thanks Skip. That did the trick!

----------
IT Admin
Iowa Board of Medicine