How to count string from 1 column based on string in another column

[janichohan]

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

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

FOR EXAMPLE:

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

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]

 

[Ron Coderre]

With your sample data list in A8:F28
And
A1: Jamshoro
A2: Sindhi
A3: Mix
A4: Urdu

This formula, copied down, returns the count of the referenced Medium for the District in A1

B2:  =COUNTIFS($C$9:$C$40,$A$1,$F$9:$F$40,$A2)

(You might need to use semicolons for delimiters, instead of commas)

Is that something you can work with?

 

[janichohan]

Dear Ron ,
thanks for answering on my post actually i juts tried your given formula but it resulting with " 0 " not counting anything.
i simple copy and paste your formula.

 

[Ron Coderre]

Does your test worksheet match the example I posted?

For example:
A1: a district to match.....Jamshoro
A2: a Medium to match....Sindh

This formula returns the count of Mediums matching "Sindh" in the Jamshoro district:

B2:  =COUNTIFS($C$9:$C$40,$A$1,$F$9:$F$40,$A2)

or...if your region uses semicolons as a delimiter

B2: =COUNTIFS($C$9:$C$40;$A$1;$F$9:$F$40;$A2)

 

[janichohan]

dear john i figure out where i was making problem now its working fine

thanks for showing interest in guiding me the problem is now solved

the only thing which i am curious about is that can it be possible i pas the string in formula like "=jamshoro" and other one "=Matiari" and it compute results.

the formula you tell me is absolutely working fine except i need to change every-time the district name...

i truly gratitude you in helping me.

regards
jibran

 

[Ron Coderre]

If the district name will change...
Why would you want to embed it into the formula? If you do that, you need to edit every formula whenever the District changes.
With the formula I posted, you only need to enter the District in A1 and the dependent formulas return counts for the new district.

 

[janichohan]

hmmmm sounds good ok i will go with your defined formula Ron
okk thanks for this
stay blessed.




bests,
jibran