How do I set up a COUNTIF with more than one condition?
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 |
How do I set up a COUNTIF with more than one condition?
See if something like the following will work for you.
=SUMPRODUCT((B1:B10="Dsci")*(C1:C10="EER")*(D1:D10 ="RA")) Mark Gail wrote: 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 |
How do I set up a COUNTIF with more than one condition?
It looks like you are looking for SumProduct. Check out this link...
http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- HTH... Jim Thomlinson "Gail" wrote: 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 |
How do I set up a COUNTIF with more than one condition?
=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/assistan...lconPT101.aspx Gail wrote: 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 -- Dave Peterson |
How do I set up a COUNTIF with more than one condition?
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" wrote: See if something like the following will work for you. =SUMPRODUCT((B1:B10="Dsci")*(C1:C10="EER")*(D1:D10 ="RA")) Mark Gail wrote: 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 |
All times are GMT +1. The time now is 02:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com