Count specific values in an unsorted, random data base

[NewExcel25]

How does one count the number of x and y values (occurring in same row) in this array?

Time1 x a b c d
Time2 b c x y a
Time3 x a y d b
Time4 a b c d e
Time5 y d a x d
Time6 c y a d b

Note all of the variables above are positive numbers.

The answer should be 3.

Thank you,

 

[Aladin Akyurek]

How does one count the number of x and y values (occurring in same row) in this array?

Time1 x a b c d
Time2 b c x y a
Time3 x a y d b
Time4 a b c d e
Time5 y d a x d
Time6 c y a d b

Note all of the variables above are positive numbers.

The answer should be 3.

Thank you,

Data is in A2:F7, with the row labels in A2:A7 and numbers in B2:F7.

x and y, the numbers to look for, are in H2:I2.

K2, control+shift+enter, not just enter:

=SUM((MMULT((COUNTIF(OFFSET(B2:F7,ROW(B2:F7)-ROW(B2),0,1),H2:I2)>0)+0,
    TRANSPOSE(COLUMN(H2:I2)>0)+0)=COUNT(H2:I2))+0)

following a solution to a problem posed by Stephen_IV...

 

[NewExcel25]

Data is in A2:F7, with the row labels in A2:A7 and numbers in B2:F7.

x and y, the numbers to look for, are in H2:I2.

K2, control+shift+enter, not just enter:

=SUM((MMULT((COUNTIF(OFFSET(B2:F7,ROW(B2:F7)-ROW(B2),0,1),H2:I2)>0)+0,
    TRANSPOSE(COLUMN(H2:I2)>0)+0)=COUNT(H2:I2))+0)

following a solution to a problem posed by Stephen_IV...

Wow, thank you very much. As a followup, if the values for H2 and I2 are not next to each other? i.e., a comparison of whole range of values.
So, on another sheet, 200 values across and 200 values down. The above equation would be in each of the cells of the corresponding matrix.

For Cell B2:
=SUM((MMULT((COUNTIF(OFFSET(DataSet,ROW(DataSet)-ROW(DataSet),0,1),B$1:$A2)>0)+0,TRANSPOSE(COLUMN(B$1:$A2)>0)+0)=COUNT(B$1:$A2))+0)

So the question is, what is the correct syntax for B$1:$A2 part of the equation? Where DataSet is the named B2:F7.

 

[Aladin Akyurek]

Wow, thank you very much. As a followup, if the values for H2 and I2 are not next to each other? i.e., a comparison of whole range of values.
So, on another sheet, 200 values across and 200 values down. The above equation would be in each of the cells of the corresponding matrix.

For Cell B2:
=SUM((MMULT((COUNTIF(OFFSET(DataSet,ROW(DataSet)-ROW(DataSet),0,1),B$1:$A2)>0)+0,TRANSPOSE(COLUMN(B$1:$A2)>0)+0)=COUNT(B$1:$A2))+0)

So the question is, what is the correct syntax for B$1:$A2 part of the equation? Where DataSet is the named B2:F7.

Please try to be specific by means of a scaled-down but representative data.

 

[NewExcel25]

This is what I was using for a simple data test case tab:
9/1/2013 1 14 2 40 53 20
9/2/2013 16 2 24 39 42 46
9/3/2013 25 32 35 50 1 46
9/4/2013 4 22 3 2 38 42
9/5/2013 2 7 36 42 44 39

Dataset = B2:G5

Test Case Tab:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]39[/TD]
[TD][TABLE="width: 187"]
<tbody>[TR]
[TD="class: xl65, width: 187"]Formula referencing B1 & A2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 187"]
<tbody>[TR]
[TD="class: xl65, width: 187"]Formula referencing C1 & A2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD][TABLE="width: 187"]
<tbody>[TR]
[TD="class: xl65, width: 187"]Formula referencing B1 & A3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 187"]
<tbody>[TR]
[TD="class: xl65, width: 187"]Formula referencing C1 & A3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

This is what I was using for a simple data test case tab:
9/1/2013 1 14 2 40 53 20
9/2/2013 16 2 24 39 42 46
9/3/2013 25 32 35 50 1 46
9/4/2013 4 22 3 2 38 42
9/5/2013 2 7 36 42 44 39

Dataset = B2:G5

Test Case Tab:
[TABLE="width: 500"]
<TBODY>[TR]
[TD][/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]39
[/TD]
[TD][TABLE="width: 187"]
<TBODY>[TR]
[TD="class: xl65, width: 187"]Formula referencing B1 & A2
[/TD]
[/TR]
</TBODY>[/TABLE]

[/TD]
[TD][TABLE="width: 187"]
<TBODY>[TR]
[TD="class: xl65, width: 187"]Formula referencing C1 & A2
[/TD]
[/TR]
</TBODY>[/TABLE]

[/TD]
[/TR]
[TR]
[TD]50
[/TD]
[TD][TABLE="width: 187"]
<TBODY>[TR]
[TD="class: xl65, width: 187"]Formula referencing B1 & A3
[/TD]
[/TR]
</TBODY>[/TABLE]

[/TD]
[TD][TABLE="width: 187"]
<TBODY>[TR]
[TD="class: xl65, width: 187"]Formula referencing C1 & A3
[/TD]
[/TR]
</TBODY>[/TABLE]

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

[TABLE="width: 144"]
<TBODY>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 64, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent, align: right"]2
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]39
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]50
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0
[/TD]
[/TR]
</TBODY>[/TABLE]

Since we have 2 item sets to test for:

B2, control+shift+enter, not just enter, copy across, and down:

=SUM((MMULT((COUNTIF(OFFSET(DataSet,ROW(DataSet)-ROW(B2),0,1),
  CHOOSE({1,2},B$1,$A2))>0)+0,{1;1})=2)+0)

 

[NewExcel25]

Hurray! Thank you, that works!.

 

[Aladin Akyurek]

Hurray! Thank you, that works!.

You are welcome.

Since we use a named range, it's nicer to express the ROW(B2) bit also as a named range, that is:

ROW(INDEX(DataSet,1,1))

The formula in full:

=SUM((MMULT((COUNTIF(OFFSET(DataSet,ROW(DataSet)-ROW(INDEX(DataSet,1,1)),0,1),
  CHOOSE({1,2},B$1,$A2))>0)+0,{1;1})=2)+0)