How do I set up a COUNTIF with more than one condition?

G

Gail

I'm using Excel [MS Office 2000 Professional in Windows XP] to list new and
continuing students and want to set up a summary sheet that captures numbers
of students by category. The lists are set up with a column each for degree
program, concentration, specific interest, admission date, graduation date,
etc.:

Student Name / degree / concentration / interest ...

In the summary sheet, I want to tally a count of students in a cross-section
of categories -- the number of DSci students, for example, in the EER
concentration who have a specific interest in RA.

Q1: How can I set up a COUNTIF to include a number of conditions -- that
is, to count those entries where (value in col B = DSci) and (value in col C
= EER) and (value in col D = RA)?
Q2: is there a different way to get at cross-category counts without using
COUNTIF?

Gail
 
M

Mark Driscol

See if something like the following will work for you.

=SUMPRODUCT((B1:B10="Dsci")*(C1:C10="EER")*(D1:D10="RA"))


Mark
 
D

Dave Peterson

=sumproduct(--(a1:a10="something"),--(b1:b10="somethingelse"))
(would be one way to count two criteria

Just keep adding more arguments to that formula--and change the range--but don't
use the whole column.

But I think I'd use data|pivottable instead of a bunch of =sumproduct()
formulas.

If you want to read more about pivottables...

Here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx
I'm using Excel [MS Office 2000 Professional in Windows XP] to list new and
continuing students and want to set up a summary sheet that captures numbers
of students by category. The lists are set up with a column each for degree
program, concentration, specific interest, admission date, graduation date,
etc.:

Student Name / degree / concentration / interest ...

In the summary sheet, I want to tally a count of students in a cross-section
of categories -- the number of DSci students, for example, in the EER
concentration who have a specific interest in RA.

Q1: How can I set up a COUNTIF to include a number of conditions -- that
is, to count those entries where (value in col B = DSci) and (value in col C
= EER) and (value in col D = RA)?
Q2: is there a different way to get at cross-category counts without using
COUNTIF?

Gail
 
G

Gail

Most excellent! Thank you, Mark, that works like a charm. And looks like I
could go on extending the set of conditions -- very nifty!
Gail

Mark Driscol said:
See if something like the following will work for you.

=SUMPRODUCT((B1:B10="Dsci")*(C1:C10="EER")*(D1:D10="RA"))


Mark

I'm using Excel [MS Office 2000 Professional in Windows XP] to list new and
continuing students and want to set up a summary sheet that captures numbers
of students by category. The lists are set up with a column each for degree
program, concentration, specific interest, admission date, graduation date,
etc.:

Student Name / degree / concentration / interest ...

In the summary sheet, I want to tally a count of students in a cross-section
of categories -- the number of DSci students, for example, in the EER
concentration who have a specific interest in RA.

Q1: How can I set up a COUNTIF to include a number of conditions -- that
is, to count those entries where (value in col B = DSci) and (value in col C
= EER) and (value in col D = RA)?
Q2: is there a different way to get at cross-category counts without using
COUNTIF?

Gail
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top