Countifs function with find cell value in different rows

[nautyraj1]

Hi,

Please help me to solve this.

[TABLE="width: 448"]
<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 64"]1[/TD]
[TD="class: xl66, width: 64"]A[/TD]
[TD="class: xl66, width: 64"]B[/TD]
[TD="class: xl66, width: 64"]C[/TD]
[TD="class: xl66, width: 64"]D[/TD]
[TD="class: xl66, width: 64"]E[/TD]
[TD="class: xl66, width: 64"]F[/TD]
[/TR]
[TR]
[TD="class: xl66"]2[/TD]
[TD="class: xl66"]Code[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]3[/TD]
[TD="class: xl65"]1111[/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]B[/TD]
[/TR]
[TR]
[TD="class: xl66"]4[/TD]
[TD="class: xl65"]1112[/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]B[/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]A[/TD]
[/TR]
[TR]
[TD="class: xl66"]5[/TD]
[TD="class: xl65"]1113[/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]B[/TD]
[TD="class: xl66"]B[/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]A[/TD]
[/TR]
[TR]
[TD="class: xl66"]6[/TD]
[TD="class: xl65"]1114[/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]B[/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]B[/TD]
[/TR]
[TR]
[TD="class: xl66"]7[/TD]
[TD="class: xl65"]1115[/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]B[/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl66"]A[/TD]
[/TR]
[TR]
[TD="class: xl66"]8[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]9[/TD]
[TD="class: xl66"]1111[/TD]
[TD="class: xl66"]*[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]10[/TD]
[TD="class: xl66"]1113[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]11[/TD]
[TD="class: xl66"]1112[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]12[/TD]
[TD="class: xl66"]1115[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]13[/TD]
[TD="class: xl66"]1111[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]14[/TD]
[TD="class: xl66"]1114[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
</tbody>[/TABLE]

I want to count how many cells contains "A" using countif function and change the number of count according to Cell value of A9 to A14. Means if cell A9 is 1111 then countif function will apply on B3 to F3 and if Cell value will change then it automatically apply countif function on the same row with same "Code".

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

Code​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

1111​

[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

1112​

[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

1113​

[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]A[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

1114​

[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

1115​

[/TD]
[TD]A[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]A[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

1111​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

1113​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

1112​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]

1115​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]

1111​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]

1114​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


B8=SUMPRODUCT(($A$2:$A$6=$A8)*($B$2:$F$6=$B$2)) copy down

 

[Aladin Akyurek]

In B9 enter and copy down:

=COUNTIFS(INDEX($B$3:$F$7,MATCH($A9,$A$3:$A$7,0),0),"A")

 

[nautyraj1]

Thank you so much

 

[Aladin Akyurek]

Thank you so much

You are welcome.