COUNTIFS Dynamic Column

[tobermory]

Hello there,

I want to use the COUNTIFS with a dynamic columns. Assuming the below is the data set in Sheet1.

<tbody>
[TD="class: xl68"][/TD]
[TD="class: xl68"]Test 1[/TD]
[TD="class: xl68"]Test 2[/TD]
[TD="class: xl68, width: 61"]Test 3[/TD]
[TD="class: xl68, width: 61"]Test 4[/TD]
[TD="class: xl68, width: 61"]Test 5[/TD]

[TD="class: xl69"]Person 1[/TD]
[TD="class: xl69, align: right"]6[/TD]
[TD="class: xl69, align: right"]2[/TD]
[TD="class: xl69, align: right"]1[/TD]
[TD="class: xl69, align: right"]2[/TD]
[TD="class: xl69, align: right"]4[/TD]

[TD="class: xl69"]Person 2[/TD]
[TD="class: xl69, align: right"]0[/TD]
[TD="class: xl69, align: right"]1[/TD]
[TD="class: xl69, align: right"]3[/TD]
[TD="class: xl69, align: right"]0[/TD]
[TD="class: xl69, align: right"]7[/TD]

[TD="class: xl69"]Person 3[/TD]
[TD="class: xl69, align: right"]4[/TD]
[TD="class: xl69, align: right"]2[/TD]
[TD="class: xl69, align: right"]3[/TD]
[TD="class: xl69, align: right"]3[/TD]
[TD="class: xl69, align: right"]3[/TD]

[TD="class: xl69"]Person 4[/TD]
[TD="class: xl69, align: right"]4[/TD]
[TD="class: xl69, align: right"]3[/TD]
[TD="class: xl69, align: right"]4[/TD]
[TD="class: xl69, align: right"]3[/TD]
[TD="class: xl69, align: right"]4[/TD]

[TD="class: xl69"]Person 5[/TD]
[TD="class: xl69, align: right"]4[/TD]
[TD="class: xl69, align: right"]3[/TD]
[TD="class: xl69, align: right"]4[/TD]
[TD="class: xl69, align: right"]2[/TD]
[TD="class: xl69, align: right"]5[/TD]

[TD="class: xl69"]Person 6[/TD]
[TD="class: xl69, align: right"]4[/TD]
[TD="class: xl69, align: right"]2[/TD]
[TD="class: xl69, align: right"]4[/TD]
[TD="class: xl69, align: right"]3[/TD]
[TD="class: xl69, align: right"]4[/TD]

[TD="class: xl69"]Person 7[/TD]
[TD="class: xl69, align: right"]5[/TD]
[TD="class: xl69, align: right"]3[/TD]
[TD="class: xl69, align: right"]5[/TD]
[TD="class: xl69, align: right"]3[/TD]
[TD="class: xl69, align: right"]4[/TD]

[TD="class: xl69"]Person 8[/TD]
[TD="class: xl69, align: right"]4[/TD]
[TD="class: xl69, align: right"]2[/TD]
[TD="class: xl69, align: right"]5[/TD]
[TD="class: xl69, align: right"]3[/TD]
[TD="class: xl69, align: right"]6[/TD]

[TD="class: xl69"]Person 9[/TD]
[TD="class: xl69, align: right"]7[/TD]
[TD="class: xl69, align: right"]3[/TD]
[TD="class: xl69, align: right"]1[/TD]
[TD="class: xl69, align: right"]4[/TD]
[TD="class: xl69, align: right"]3[/TD]

[TD="class: xl69"]Person 10[/TD]
[TD="class: xl69, align: right"]4[/TD]
[TD="class: xl69, align: right"]4[/TD]
[TD="class: xl69, align: right"]3[/TD]
[TD="class: xl69, align: right"]1[/TD]
[TD="class: xl69, align: right"]3[/TD]

</tbody>

In Sheet 2, the 'Tests' are listed the other way around, down a column, not across a row. i.e. 'Test 1' in A2, 'Test 2' in A3, 'Test 3' in A4 etc. How do I create a dynamic COUNTIF in Column C based on lookup/index match in column B? i.e. How many 4s were there in Test 1?

<tbody>
[TD="class: xl68"][/TD]
[TD="class: xl68, align: right"]0[/TD]
[TD="class: xl68, width: 66, align: right"]1[/TD]
[TD="class: xl68, width: 66, align: right"]2[/TD]
[TD="class: xl68, width: 66, align: right"]3[/TD]
[TD="class: xl68, width: 66, align: right"]4[/TD]
[TD="class: xl68, width: 66, align: right"]5[/TD]
[TD="class: xl68, width: 66, align: right"]6[/TD]
[TD="class: xl68, width: 66, align: right"]7[/TD]
[TD="class: xl68, width: 66, align: right"]8[/TD]
[TD="class: xl68, width: 66, align: right"]9[/TD]
[TD="class: xl68, width: 66, align: right"]10[/TD]

[TD="class: xl69"]Test 1[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]

[TD="class: xl69"]Test 2[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]

[TD="class: xl69"]Test 3[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]

[TD="class: xl69"]Test 4[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]

[TD="class: xl69"]Test 5[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]
[TD="class: xl69"]countif[/TD]

</tbody>

I hope that makes sense, many thanks in advance for your answers.

 

[James006]

Hi,

In Sheet 2 cell B2 ... you can test .

=COUNTIF(OFFSET(Sheet1!$B$2:$B$11,0,ROW()-2),COLUMN()-2)

Hope this will help

 

[ExcelElliott]

This would also work:

=COUNTIF(INDEX($B$1:$F$11,,MATCH($J3,$B$1:$F$1,0)),K$2)

 

[ExcelElliott]

Sorry, didn't read the full post. Since you need this on Sheet2:

=COUNTIF(INDEX('Sheet1'!$B$1:$F$11,,MATCH($A2,'Sheet1'!$B$1:$F$1,0)),B$1)

 

[bprime]

This would also work:

=COUNTIF(INDEX($B$1:$F$11,,MATCH($J3,$B$1:$F$1,0)),K$2)

Thanks so much

 

[bprime]

In the spirit of community contribution, if you have another column to look for in addition you'd just add another index stipulation to countif


=iferror(COUNTIFS(INDEX($E$4:$BT,,MATCH(P4,$E$3:$BT3,0)),TRue,INDEX($E$4:$BT,,MATCH($P$3,$E$3:$BT3,0)),TRue),"")