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]

Academy	Hank Pym	Hawkeye	Quicksilver	Tigra
Agents of SHIELD	Black Widow	Hawkeye	Mockingbird
Ambulance Chasers	Daredevil	She-Hulk
Anything's Possible	Black Cat	Domino	Scarlet Witch
Arachnophobia	Black Widow	Spider-Man	Spider-Woman

<tbody>
</tbody>

 

[Aladin Akyurek]

Academy	Hank Pym	Hawkeye	Quicksilver	Tigra
Agents of SHIELD	Black Widow	Hawkeye	Mockingbird
Ambulance Chasers	Daredevil	She-Hulk
Anything's Possible	Black Cat	Domino	Scarlet Witch
Arachnophobia	Black Widow	Spider-Man	Spider-Woman

<TBODY>
</TBODY>

It seems there isn't a header row!...

Would you formulate some example calculations you would want to carry out on this sample along with the results you want to see?

 

[Aguadiablo]

It seems there isn't a header row!...

Would you formulate some example calculations you would want to carry out on this sample along with the results you want to see?

Well the heading row would read something like

Group Name

Hero 1

Hero 2

Hero 3

Hero 4

<tbody>
</tbody>

As for example calculations the only way of calculating what I want to do is:

=COUNTIFS(B1:B5,"Hank Pym",C1:C5,"Hawkeye")+COUNTIFS(C1:C5,"Hank Pym",D1:D5,"Hawkeye")+COUNTIFS(D1:D5,"Hank Pym",E1:E5,"Hawkeye")
=COUNTIFS(B1:B5,"Black Widow",C1:C5,"Hawkeye")+COUNTIFS(C1:C5,"Black Widow",D1:D5,"Hawkeye")+COUNTIFS(D1:D5,"Black Widow",E1:E5,"Hawkeye")

Which give me the correct answer of one, but this does not work if I entered the combinations the other way round.

=COUNTIFS(B1:B5,"Hawkeye",C1:C5,"Black Widow")+COUNTIFS(C1:C5,"Hawkeye",D1:D5,"Black Widow")+COUNTIFS(D1:D5,"Hawkeye",E1:E5,"Black Widow")
The above formula gives an answer of 0 which is incorrect.

And I have no idea how to edit those formulas in order to ignore some combinations. I.e. count Black Widow and Hawkeye or Black Widow and Mockingbird in the second row but not Hawkeye and Mockingbird.

=COUNTIFS(B1:B5,"Hawkeye",C1:C5,"Mockingbird")+COUNTIFS(C1:C5,"Hawkeye",D1:D5,"Mockingbird")+COUNTIFS(D1:D5,"Hawkeye",E1:E5,"Mockingbird")

This gives me one but it should be 0 with this sample but it gives me 1. And with the whole table it should read 6.

 

[Aladin Akyurek]

Thanks for the header.

However, I didn't want to ask formulas. Rather, what do you want to know, given the sample we now have?

 

[Aguadiablo]

Well what I want is to have a formula that will count every time a combination occurs in the rows of data.

So for the sample

Hank Pym & Hawkeye - 1
Hank Pym & Quicksilver - 1
Hank Pym & Tigra - 1
Hawkeye & Quicksilver - 1
Hawkeye & Tigra - 1
Hawkeye & Black Widow - 1
Hawkeye & Mockingbird - 0
Quicksilver & Tigra - 1
Tigra & Quicksilver - 1
Black Widow & Mockingbird - 1
Black Widow & Tigra - 0
Domino & Scarlet Witch - 1
Domino & Black Widow -0

And for it to continue for every possible combination.

 

[Drrellik]

How about laying out your sheet, which you said would grow over time like this. Excel 2012

	A	B	C	D	E	F
1		Hero 1 Input	Hank Pym	Hero 2 Input	Quicksilver	TRUE
2	Group Name	Hero 1	Hero 2	Hero 3	Hero4	FALSE
3	Academy	Hank Pym	Hawkeye	Quicksilver	Tigra	FALSE
4	Agents of SHIELD	Black Widow	Hawkeye	Mockingbird		FALSE
5	Ambulance Chasers	Daredevil	She-Hulk			FALSE
6	Anything's Possible	Black Cat	Domino	Scarlet Witch		FALSE
7	Arachnophobia	Black Widow	Spider-Man	Spider-Woman		FALSE

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula F1 =AND(COUNTIF(B3:E3,C1),COUNTIF(B3:E3,E1)) G1 =COUNTIF(F:F,"true") F2 =AND(COUNTIF(B4:E4,C2),COUNTIF(B4:E4,E2)) F3 =AND(COUNTIF(B5:E5,C3),COUNTIF(B5:E5,E3)) F4 =AND(COUNTIF(B6:E6,C4),COUNTIF(B6:E6,E4)) F5 =AND(COUNTIF(B7:E7,C5),COUNTIF(B7:E7,E5)) F6 =AND(COUNTIF(B8:E8,C6),COUNTIF(B8:E8,E6)) F7 =AND(COUNTIF(B9:E9,C7),COUNTIF(B9:E9,E7)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

For some reason G1 did not post but the answer was 1. you can modify the =and(... formula to accommodate a 3rd or 4th hero if you chose to. and because your sheet will grow over time you can always copy the formula down and move F:F to the right as you need. Simply but hopefully puts you on track. I like simply, mostly because that is all I know.... LOL

 

[Aladin Akyurek]

Well what I want is to have a formula that will count every time a combination occurs in the rows of data.

So for the sample

Hank Pym & Hawkeye - 1
Hank Pym & Quicksilver - 1
Hank Pym & Tigra - 1
Hawkeye & Quicksilver - 1
Hawkeye & Tigra - 1
Hawkeye & Black Widow - 1
Hawkeye & Mockingbird - 0
Quicksilver & Tigra - 1
Tigra & Quicksilver - 1
Black Widow & Mockingbird - 1
Black Widow & Tigra - 0
Domino & Scarlet Witch - 1
Domino & Black Widow -0

And for it to continue for every possible combination.

A:E houses the sample...

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

<COLGROUP><COL style="WIDTH: 167pt; mso-width-source: userset; mso-width-alt: 7936" width=223><COL style="WIDTH: 103pt; mso-width-source: userset; mso-width-alt: 4864" width=137><COL style="WIDTH: 141pt; mso-width-source: userset; mso-width-alt: 6684" width=188><COL style="WIDTH: 136pt; mso-width-source: userset; mso-width-alt: 6428" width=181><COL style="WIDTH: 119pt; mso-width-source: userset; mso-width-alt: 5632" width=158><TBODY>
</TBODY>

G:I houses the processing...

Hank Pym	Hawkeye	1
Hank Pym	Quicksilver	1
Hank Pym	Tigra	1
Hawkeye	Quicksilver	1
Hawkeye	Tigra	1
Hawkeye	Black Widow	1
Hawkeye	Mockingbird	1
Quicksilver	Tigra	1
Tigra	Quicksilver	1
Black Widow	Mockingbird	1
Black Widow	Tigra	0
Domino	Scarlet Witch	1
Domino	Black Widow	0

<COLGROUP><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: 48pt" width=64><TBODY>
</TBODY>

I1, control+shift+enter, not just enter, and copy down:

=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)

 

[Drrellik]

That's not fair Aladin, you could have waited a little longer before trumping my post...

 

[Aguadiablo]

A:E houses the sample...

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

<tbody>
</tbody>

G:I houses the processing...

Hank Pym	Hawkeye	1
Hank Pym	Quicksilver	1
Hank Pym	Tigra	1
Hawkeye	Quicksilver	1
Hawkeye	Tigra	1
Hawkeye	Black Widow	1
Hawkeye	Mockingbird	1
Quicksilver	Tigra	1
Tigra	Quicksilver	1
Black Widow	Mockingbird	1
Black Widow	Tigra	0
Domino	Scarlet Witch	1
Domino	Black Widow	0

<tbody>
</tbody>

I1, control+shift+enter, not just enter, and copy down:

=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)

Thank you for this formula not only does it seem to work just for the sample but for the whole table. However, would it be possible for you to give a bit of an explanation of how it works, just so I have an idea how it works.

 

[Aladin Akyurek]

Thank you for this formula not only does it seem to work just for the sample but for the whole table. However, would it be possible for you to give a bit of an explanation of how it works, just so I have an idea how it works.

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)
==> Evaluating the COUNTIF bit for the pair in G1:H1,
      we get an array of 2 columns of counts...
SUM((MMULT(({1,1;0,1;0,0;0,0;0,0}>0)+0,TRANSPOSE(COLUMN(G1:H1)^0))=COUNTIF(G1:H1,"?*"))+0)
==> Evaluating the TRANSPOSE bit, 
      we get an array of 2 rows (an identity matrix/vector)...
SUM((MMULT(({1,1;0,1;0,0;0,0;0,0}>0)+0,{1;1})=COUNTIF(G1:H1,"?*"))+0)
==> Evaluating the > 0 comparison, we get...
SUM((MMULT(({TRUE,TRUE;FALSE,TRUE;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE})+0,{1;1})=COUNTIF(G1:H1,"?*"))+0)
==> Evaluating the +0 transformation bit 
      involving TRUE and FALSE values, we get...
SUM((MMULT({1,1;0,1;0,0;0,0;0,0},{1;1})=COUNTIF(G1:H1,"?*"))+0)
==> Evaluating MMULT, the matrix multiplication, we get...
SUM(({2;1;0;0;0}=COUNTIF(G1:H1,"?*"))+0)
==> Evaluating the COUNTIF bit, we arrive at...
SUM(({2;1;0;0;0}=2)+0)
==> Evaluating the = comparison, we have:
SUM({TRUE;FALSE;FALSE;FALSE;FALSE}+0)
==> Evaluating the +0 transformation, we get...
SUM({1;0;0;0;0})
==> 1
which says that we have just 1 row with items specified in G1:H1.

Hope this helps a bit.