COUNTIF statement - how can i use multiple args/ criteria
Posted by Daniel Cremin on February 05, 2002 5:55 AM
Hi ive already solved this requirement to be able to find out how many members of each separate class (different names in teacher cell class) are achieving each poss grade through a filtering macro that gets them to another page where there is a countif statement waiting in the wings. However just for general interest id like to know if i can somehow get Excel to only count grade A (this is the if argument, for studenta in a certain class (e.g. Mr Cremin group)- the prob is that the cells arent re-sorted by teacher name so i cant simply have countifs for pre-specified cells all with grades of students from specific classes - the idea of the project is that students can be entered at ne time.
Posted by Aladin Akyurek on February 05, 2002 6:05 AM
Daniel --
Care to post 10 rows of (fake) data along with what kind of count you want?
If you're inclined to do so, activate an empty cell, type =, select 10 rows of data, and hit F9. Go the Formula Bar, copy what you see, and post it in the follow-up.
Aladin
==========
Posted by Daniel on February 05, 2002 6:11 AM
Thanks for the offer of help but im just going out this second and dont have the time to do it.
Posted by Larry Kramer on February 10, 2002 3:42 PM
Assume you have column named teacher and a column named grade. A count of Mr. Cremin's A students can be got by the array formula:
{=SUM((teacher="Cremin")*(grade="A"))}
You can generalize this by replacing the teacher's name with a cell that contains it, ditto the grade to search for.
You enter an array formula by typing the formula without the braces and, instead of hitting ENTER, hitting Ctrl-Shft-Enter
