Reputation: 1270
Here is a simple sample of my data:
I want to get the outcome. The logic is that we have 4 pairs data highlighted in different colours.
I want to get the outcome as follows. If both colours are "Y", I would aggregate them. For example, in row 2, the green has a value of "Y" ( both have "Y", so we could get 1 in the outcome) In Row 3, yellow and orange have a value of "Y", so we get a value of 2 in outcome.
I have tried the following function, but it did not work for me.
IF(OR(AND(A2="N", B2="N"), AND(C2="N", D2="N"),AND(E2="N", F2="N"),AND(G2="N", H2="N")), "0", "1")
Upvotes: 0
Views: 150
Reputation: 75840
You could try:
Formula in I2
, using Excel O365:
=SUM((INDEX(A2:H2,{1,3,5,7})="Y")*(INDEX(A2:H2,{2,4,6,8})="Y"))
In Excel prior to Excel O365, try:
=SUMPRODUCT((INDEX(A2:H2,N(IF(1,{1,3,5,7})))="Y")*(INDEX(A2:H2,N(IF(1,{2,4,6,8})))="Y"))
Upvotes: 2