MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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