user330
user330

Reputation: 1270

Sorting and indexing multiple conditions in Excel

Here is a simple sample of my data:

enter image description here

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

Answers (1)

JvdV
JvdV

Reputation: 75840

You could try:

enter image description here

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

Related Questions