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????

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

 

[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
S #	Province	District	School Annex	Gender	Medium
Sindh	Jamshoro	GGPS	Girls	Sindhi
Sindh	Jamshoro	GBPS	Boys	Sindhi
Sindh	Jamshoro	GPS	Mix	Sindhi
Sindh	Jamshoro	GPS	Mix	Sindhi
Sindh	Jamshoro	GGPS	Girls	Mix	Jamshoro	Mix
Sindh	Jamshoro	GBPS	Boys	Mix	Sindhi
Sindh	Jamshoro	GBPS	Boys	Sindhi	Urdu
Sindh	Jamshoro	GGPS	Girls	Urdu
Sindh	Jamshoro	GPS	Mix	Urdu
Sindh	Jamshoro	GGPS	Girls	Urdu
Sindh	Matiari	GBPS	Boys	Sindhi
Sindh	Matiari	GPS	Mix	Mix
Sindh	Matiari	GPS	Mix	Sindhi
Sindh	Matiari	GBPS	Boys	Sindhi
Sindh	Matiari	GBPS	Boys	Sindhi
Sindh	Matiari	GBPS	Boys	Mix
Sindh	Matiari	GGPS	Girls	Sindhi
Sindh	Matiari	GPS	Mix	Urdu
Sindh	Matiari	GPS	Mix	Mix
Sindh	Matiari	GPS	Mix	Urdu

<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #D9D9D9"]Summary[/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]
[TD="bgcolor: #D9D9D9, align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #0070C0"]District[/TD]
[TD="bgcolor: #0070C0"]Medium[/TD]
[TD="bgcolor: #E2EFDA"]Count[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"][/TD]

[TD="bgcolor: #E2EFDA, align: right"]2[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #E2EFDA, align: right"]5[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #E2EFDA, align: right"]3[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]15[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]17[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]18[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]20[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]J6[/TH]
[TD="align: left"]=COUNTIFS($C$2:$C$21,$H$6,$F$2:$F$21,I6)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]J7[/TH]
[TD="align: left"]=COUNTIFS($C$2:$C$21,$H$6,$F$2:$F$21,I7)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]J8[/TH]
[TD="align: left"]=COUNTIFS($C$2:$C$21,$H$6,$F$2:$F$21,I8)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.