How to count SIMILAR DATA from 1 column based on string in 2nd column

[janichohan]

HI GUYS I AM NEW TO THIS FORUM I AM BALDY STUCK IN MAKING FORMULA IN EXCEL 2007.
CAN ANY BODY TELL ME ?

THIS IS THE SAMPLE DATA BELOW. I WANT TO COUNT THE " MEDIUM CELL VALUES" BASED ON " DISTRICT COLUMN VALUES"

FOR EXAMPLE: IT WILL GAVE ME THE FINAL RESULT AS.

DISTRICT--> JAMSHORO HAD FOLLOWING COUNT BASED ON column MEDIUM
SINDHI ( 5 TIMES)
MIX (2 TIMES)
URDU ( 3 TIMES)

AND SAME FOR REST OF DATA ACTUALLY I WANT OT USE THIS FORMULA IN SUMMARY ITS NOT NECESSARY THAT THIS FORMULA RUNS ON EACH ROW.

CAN ANYBODY HELP ME HOW I PUT THIS THING INTO FORMULA????

S # Province District School Annex Gender Medium 1 Sindh Jamshoro GGPS Girls Sindhi 2 Sindh Jamshoro GBPS Boys Sindhi 3 Sindh Jamshoro GPS Mix Sindhi 4 Sindh Jamshoro GPS Mix Sindhi 5 Sindh Jamshoro GGPS Girls Mix 6 Sindh Jamshoro GBPS Boys Mix 7 Sindh Jamshoro GBPS Boys Sindhi 8 Sindh Jamshoro GGPS Girls Urdu 9 Sindh Jamshoro GPS Mix Urdu 10 Sindh Jamshoro GGPS Girls Urdu 11 Sindh Matiari GBPS Boys Sindhi 12 Sindh Matiari GPS Mix Mix 13 Sindh Matiari GPS Mix Sindhi 14 Sindh Matiari GBPS Boys Sindhi 15 Sindh Matiari GBPS Boys Sindhi 16 Sindh Matiari GBPS Boys Mix 17 Sindh Matiari GGPS Girls Sindhi 18 Sindh Matiari GPS Mix Urdu 19 Sindh Matiari GPS Mix Mix 20 Sindh Matiari GPS Mix Urdu <tbody> </tbody>

<tbody>
</tbody>

 

[DRSteele]

I think this might be what you need. If you need more criteria listed other than the four listed in your example, just expand the table or consider using a Pivot Table.

[PS Please don't type in all-caps because it annoys readers.]

	A	B	C	D	E	F	G	H	I	J
1	S #	Province	District	School Annex	Gender	Medium
2	1	Sindh	Jamshoro	GGPS	Girls	Sindhi
3	2	Sindh	Jamshoro	GBPS	Boys	Sindhi
4	3	Sindh	Jamshoro	GPS	Mix	Sindhi		Summary
5	4	Sindh	Jamshoro	GPS	Mix	Sindhi		District	Medium	Count
6	5	Sindh	Jamshoro	GGPS	Girls	Mix		Jamshoro	Mix	2
7	6	Sindh	Jamshoro	GBPS	Boys	Mix			Sindhi	5
8	7	Sindh	Jamshoro	GBPS	Boys	Sindhi			Urdu	3
9	8	Sindh	Jamshoro	GGPS	Girls	Urdu
10	9	Sindh	Jamshoro	GPS	Mix	Urdu
11	10	Sindh	Jamshoro	GGPS	Girls	Urdu
12	11	Sindh	Matiari	GBPS	Boys	Sindhi
13	12	Sindh	Matiari	GPS	Mix	Mix
14	13	Sindh	Matiari	GPS	Mix	Sindhi
15	14	Sindh	Matiari	GBPS	Boys	Sindhi
16	15	Sindh	Matiari	GBPS	Boys	Sindhi
17	16	Sindh	Matiari	GBPS	Boys	Mix
18	17	Sindh	Matiari	GGPS	Girls	Sindhi
19	18	Sindh	Matiari	GPS	Mix	Urdu
20	19	Sindh	Matiari	GPS	Mix	Mix
21	20	Sindh	Matiari	GPS	Mix	Urdu

<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula J6 =COUNTIFS($C$2:$C$21,$H$6,$F$2:$F$21,I6) J7 =COUNTIFS($C$2:$C$21,$H$6,$F$2:$F$21,I7) J8 =COUNTIFS($C$2:$C$21,$H$6,$F$2:$F$21,I8) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[janichohan]

DRSteele,
Thanks For answering me on this, i exactly want this and it working fine
Bundle of thanks again for helping me in this

Regards
Jibran

 

[DRSteele]

Awesome. You're welcome. I'm glad I helped you move your project forward.

 

[janichohan]

can you please tell me how i can mark this post as solved on this forum . i keep on finding link but all in vain... i am new to this so didnt know well.

 

[DRSteele]

I think there is no way to do that. The usual etiquette here is to just do as you've done and write a note of thanks; it will indicate that the problem is solved.