Counting with multiple criteria and different tables

[Aguadiablo]

I have many different tables all on one worksheet, with identical data in appearing in more than one table, but never more than once per table.

It looks something like this:

Table 1 Name | Data 1 | Data 2 | Data 3 | Data 4
Table 2 Name | Data 2 | Data 4
Table 3 Name | Data 3 | Data 4
Table 4 Name | Data 2 | Data 3 | Data 4

Is there a formula that can give the me total number of times that both Data 3 and Data 4 appear together in all the tables?

I.e. Data 3 and Data 4 both appear in Table 1, 3, & 4 so the formula should give me the number 3.

 

[Aguadiablo]

Is there away of modifing the formula:


=SUM((MMULT((COUNTIF(OFFSET($B$2:$E$6,ROW($B$2:$E$6)-ROW($B$2),0,1),G1:H1)>0)+0, TRANSPOSE(COLUMN(G1:H1)^0))=COUNTIF(G1:H1,"?*"))+0)

So that the character name references in G1:H1 don't need to be in adjacent cells?
</pre>

 

[Aladin Akyurek]

Is there away of modifing the formula:


=SUM((MMULT((COUNTIF(OFFSET($B$2:$E$6,ROW($B$2:$E$6)-ROW($B$2),0,1),G1:H1)>0)+0, TRANSPOSE(COLUMN(G1:H1)^0))=COUNTIF(G1:H1,"?*"))+0)

So that the character name references in G1:H1 don't need to be in adjacent cells?

</PRE>

In which cells are they?

 

[Aguadiablo]

In which cells are they?

The first lost of references are currently C2 to DP4, and the second lot are currently B3 to B122 and then each of the horizontal references are matched to each of the vertical references. So it looks like below

	Cable	Cyclops	Daimon Hellstrom	Dr. Strange
Cable	N/A	#	#	#
Cyclops	#	N/A	#	#
Daimon Hellstrom	#	#	N/A	#
Dr. Strange	#	#	#	N/A

<tbody>
</tbody>

# = The number of times each combo appears.

This is the easiest way I could think of to match each of the 79 characters to the other 78. But over time more characters will be added.

 

[Aladin Akyurek]

The first lost of references are currently C2 to DP4, and the second lot are currently B3 to B122 and then each of the horizontal references are matched to each of the vertical references. So it looks like below

	Cable	Cyclops	Daimon Hellstrom	Dr. Strange
Cable	N/A	#	#	#
Cyclops	#	N/A	#	#
Daimon Hellstrom	#	#	N/A	#
Dr. Strange	#	#	#	N/A

<TBODY>
</TBODY>

# = The number of times each combo appears.

This is the easiest way I could think of to match each of the 79 characters to the other 78. But over time more characters will be added.

What do you mean by 'first lot' being C2:DP4 and 'second lot' B3:B122?

I looks like you want to place the criteria characters to match in a matrix structure and look for each row and column combination thereof in the data. Right? Continuing with the initial example would be more convenient...

 

[Aguadiablo]

What do you mean by 'first lot' being C2:DP4 and 'second lot' B3:B122?

I looks like you want to place the criteria characters to match in a matrix structure and look for each row and column combination thereof in the data. Right? Continuing with the initial example would be more convenient...

Yeah that's it, with the example it'd look like

	Black Cat	Black Widow	Daredevil	Domino
Black Cat	N/A	0	0	1
Black Widow	0	N/A	0	0
Daredevil	0	0	N/A	0
Domino	1	0	0	N/A

<tbody>
</tbody>

 

[Aladin Akyurek]

Yeah that's it, with the example it'd look like

	Black Cat	Black Widow	Daredevil	Domino
Black Cat	N/A			1
Black Widow		N/A
Daredevil			N/A
Domino	1			N/A

<TBODY>
</TBODY>

Group Name	Hero 1	Hero 2	Hero 3	Hero 4		Black Cat	Black Widow	Daredevil	Domino
Academy	Hank Pym	Hawkeye	Quicksilver	Tigra	Black Cat	N/A	0	0	1
Agents of SHIELD	Black Widow	Hawkeye	Mockingbird		Black Widow	0	N/A	0	0
Ambulance Chasers	Daredevil	She-Hulk			Daredevil	0	0	N/A	0
Anything's Possible	Black Cat	Domino	Scarlet Witch		Domino	1	0	0	N/A
Arachnophobia	Black Widow	Spider-Man	Spider-Woman

<COLGROUP><COL style="WIDTH: 146pt; mso-width-source: userset; mso-width-alt: 6912" width=194><COL style="WIDTH: 103pt; mso-width-source: userset; mso-width-alt: 4864" width=137><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4636" width=130><COL style="WIDTH: 115pt; mso-width-source: userset; mso-width-alt: 5461" width=154><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3640" width=102><COL style="WIDTH: 103pt; mso-width-source: userset; mso-width-alt: 4864" width=137><COL style="WIDTH: 88pt; mso-width-source: userset; mso-width-alt: 4152" width=117><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3669" width=103><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3100" width=87><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3271" width=92><TBODY>
</TBODY>

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

=IF($F2=G$1,"N/A",SUM((MMULT((COUNTIF(OFFSET($B$2:$E$6,ROW($B$2:$E$6)-ROW($B$2),0,1),
  CHOOSE({1,2},$F2,G$1))>0)+0,{1;1})=2)+0))