Count matching values from cells across a column and a row

[VanCity208]

Hi,

I'm trying to count the number of occasions when a cell in a specific column matches a corresponding cell in a specific row. So, going down the column, is there a "1" in the column and a "1" in the same position while going across a row? In other words, if the first cell in the column range contains "1" AND the first cell in the row range contains "1", then it's counted. Then, if the second cell in the column range is "1" AND the second cell in the row range contains "1", then it's counted too.

I'm trying to count reciprocated nominations (in which a person nominated a co-worker and that co-worker also nominated the person). For example, Jane's nominations in column B compared to Jane's received nominations in row 2 - the first cell/intersection of column and row is zero (no self-nominations...), but I need to count when a 1 appears in B3 AND in C2, plus in B4 AND in D2, plus in B5 AND in E2. In Jane's case, she has 1 reciprocated nomination (from Billy), and this would be counted/summed(?) in B7. Note that Sue's nomination of Jane (E2) doesn't count, because Jane didn't nominate Sue (B5). Then, I'd do the same for Billy across column C and row 3 (2 reciprocated nominations), then Bobby (0 reciprocated nominations), and then Sue (1 reciprocated nomination).

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD="align: center"]Jane[/TD]
[TD="align: center"]Billy[/TD]
[TD="align: center"]Bobby[/TD]
[TD="align: center"]Sue[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Jane[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Billy[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Bobby[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Sue[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Jane[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Billy[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I have to count these reciprocated nominations for larger groups (~25 people) in multiple offices, so I'd love to find a function that I could apply that would count these for me quickly. I've tried reading about several functions (Index, Match, VLookup), but I can't figure out how to apply them to my situation. So, maybe, I'm completely looking in the wrong direction. : / Any suggestions would be greatly appreciated!

 

[Marcelo Branco]

Maybe...


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

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

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

Jane​

[/TD]
[TD]

Billy​

[/TD]
[TD]

Bobby​

[/TD]
[TD]

Sue​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Jane​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Billy​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Bobby​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Sue​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Jane​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

Billy​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

Bobby​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

Sue​

[/TD]
[TD]

1​

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


Array formula in B7 copied down
=SUM(($A$2:$A$5=A7)*($B$2:$E$5=1)*TRANSPOSE($B$2:$E$5=1))
Ctrl+Shift+Enter, not just Enter

M.

 

[VanCity208]

Thank you!! This worked beautifully, and you have saved me SO MANY hours of work. I only wish I'd asked sooner!

Thank you for taking the time to post an answer! I really appreciate the help!

 

[Marcelo Branco]

You are very welcome. Glad to help

M.