Count ifs

[aysam]

Hi all
I have to columns. One has the names of classes
1/1. 1/2. 1/3 2/5. 2/6. 2/70.........
The other has the names of subjects
English,science , maths .....
What I want is a formula to count how many classes has got English ,
How many classes has got Science. etc ....
Waiting

 

[etaf]

what version of excel are you using - please update your profile

As you could use Unique() Filter() etc if on the later version of excel

are you able to just count the number of times "english" appears in the column
using a
COUNTIF()

I feel there maybe more to this question?
if not

Then if you want to summarise the info or you could use a pivot table

Book1
	A	B	C	D	E
1
2	1/1	english		english	3
3	1/2	science		Science	1
4	1/3 2/5	english
5	2/6
6	2/70	english
Sheet1

Cell Formulas
Range	Formula
E2:E3	E2	=COUNTIF($B$2:$B$6,D2)

 

[aysam]

First of all I am sorry but l explained wrongly what I want so I explained it again

I have two columns A and. B
Column A has names of classes in my school
eg. 1/1. 1/2. 1/3. 1/4.
2/1. 2/2. 2/3. 2/4. 2/5. etc
Column B has the names of subjects studied every time
English. Science. Maths. Arts
What l need is a formula to count how many first grade classes studied English
and how many second grade classes studied English
Hope I am clear
Using office 2010

 

[Sufiyan97]

It will be easier if you change your Class names as below

Book2
	A	B	C	D	E	F
1	Class/Grade	Subject		Class/Grade	A
2	A1	English		Subject	English
3	A2	Science		Count	1
4	A3	Maths
5	A4	Arts
6	B1	English
7	B2	Science
8	B3	Maths
9	B4	Arts
10	B5
11
12
Sheet1

Cell Formulas
Range	Formula
E3	E3	=COUNTIFS(A:A,$E$1&"*",B:B,$E$2)

 

[etaf]

what is 1st grade and 2nd grade
is the the prefix
1/
2/

Book1
	A	B	C	D	E	F
1				Grade	Subject	Number of Classes
2	1/1	english		1	english	2
3	1/2	science		1	Science	1
4	1/3	english		2	english	1
5	2/1			2	Science	0
6	2/7	english
Sheet1

Cell Formulas
Range	Formula
F2:F5	F2	=COUNTIFS($A$2:$A$6,D2&"*",$B$2:$B$6,E2)

 

[aysam]

Thank you very much
That's what I want

 

[etaf]

you are welcome