Need Formula help - count if multiple criteria exists

[cgoodson]

I need a formula which will allow me to identify the instances where Sheet1 NAME1 (in column) matches Sheet2 NAME2 (in column) (NAME2 exists multiple times in same column). For the matches, I need to count instances where trait1 = 1 (is either 1 or 0), trait2 =1 (is either 1 or 0), and trait3 = 1 (is either 1 or 0) are ALL true.

 

[Domenic]

Can you provide a sample of the data, along with the actual/expected results?

 

[cgoodson]

I want to display the results in Sheet 1 based on data in sheet2

Sheet 1 Column A has 30 unique names

Sheet 2 Column A has 30 names with 16 instances each. While the 30 names are not in the same order each time, they do appear once in each of 16 blocks. The order of the names/blocks needs to remain unchanged.
Each instance of the name has 11 traits (columns) for which the displayed result is one or zero.

Sheet 1:
Name1
Name2
Name3
Name4
...Name30

Sheet 2
Name Tr1 Tr2 Tr3 Tr4 Tr5 Tr6 Tr7 Tr8 Tr9 Tr10 Tr11
Name1 1 0 0 1 1 0 0 1 0 1 0
Name2 0 1 1 1 0 1 0 0 1 0 0
Name3 1 0 0 1 1 0 0 1 0 1 0
Name4 0 1 1 1 0 1 0 0 1 0 0
...Name30
Blank line
Blank line
Name3 1 0 0 1 1 0 0 1 0 1 0
Name2 1 1 1 1 0 1 0 0 1 0 0
Name30 1 0 0 1 1 0 0 1 0 1 0
Name1 1 1 1 1 0 1 0 0 1 1 0
...

For Name1 Tr1 I would want to see:
Find each instance of Name1 in sheet 2 where Tr8 = 1 AND Tr1 = 1 AND TR10 = 1, then count as one instance and return "1" to Sheet 1 Tr1

Expected result of Sheet 1 Name 1 Tr1 is 1 based on above data set (first instance of sheet2 name1 passes (all three true) but second instance fails (Tr8 = 0)

Thanks,

 

[Domenic]

Assuming that A1:L12 contains the data, let N2:N3 contain the unique names, such as Name1, Name2, Name3, then try the following formula which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

O2, copied dwn:

=SUM(IF($A$2:$A$12=N2,IF(MMULT(IF(ISNUMBER(MATCH($B$1:$L$1,{"Tr1","Tr8","Tr10"},0)),IF($B$2:$L$12=1,$B$2:$L$12,0),0),TRANSPOSE(COLUMN($B$2:$L$12)^0))=3,1)))

Alternatively, since it looks like the data within Columns B through L will contain 1's and 0's only, the following formula would suffice...

=SUM(IF($A$2:$A$12=N2,IF(MMULT(IF(ISNUMBER(MATCH($B$1:$L$1,{"Tr1","Tr8","Tr10"},0)),$B$2:$L$12,0),TRANSPOSE(COLUMN($B$2:$L$12)^0))=3,1)))

...which also needs to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[cgoodson]

couldn't get it to work - additional (much) information

I couldn't get this to work. I have provided additional information.

Sheet 1 is where I want to summarize the data contained in Sheet 2. Sheet 1 contains 30 unique names (A8:A37). Columns C:AA is where I want to summarize the data from sheet 2.

Sheet 2 is where the data is located. Sheet 2 contains the 30 names, each appearing 16 times in 16 blocks (each name appears one time in a block, names are listed in a different order in each block).

D2:D46 30 names in random order (name row, name row, blank row format). 2nd of 16 blocks is at D56:D100.

Sheet 2 reflects results of 5 tests. Each test has two options but only one can pass (pass = 1, fail = 0).

The tests results are located in Sheet 2 in columns as follows:
Test 1 (H/A)
Col I Result H1
Col J Result A1

Test 2 (SW/SL)
Col K Result SW1
Col L Result SL1

Test 3 (AW/AL)
Col M Result AW1
Col L Result AL1

Test 4 (O/U)
Col O Result O1
Col P Result U1

Test 5 (F/D)
Col R Result F1
Col S Result D1


I want to summarize the results from sheet 2 at Sheet 1 in the following columns:

C:C IF H1=1 AND SW1=1 AND F1 = 1 then 1 else 0
D:D IF H1=1 AND SL1=1 AND F1 = 1 then 1 else 0
F:F IF H1=1 AND SW1=1 AND D1 = 1 then 1 else 0
G:G IF H1=1 AND SL1 AND D1 = 1 then 1 else 0
I:I IF H1=1 AND AW1=1 AND F1 = 1 then 1 else 0
J:J IF H1=1 AND AL1=1 AND F1 = 1 then 1 else 0
L:L IF H1=1 AND AW1=1 AND D1 = 1 then 1 else 0
M:M IF H1=1 AND AL1 AND D1 = 1 then 1 else 0

P:P IF A1=1 AND SW1=1 AND F1 = 1 then 1 else 0
Q:Q IF A1=1 AND SL1=1 AND F1 = 1 then 1 else 0
S:S IF A1=1 AND SW1=1 AND D1 = 1 then 1 else 0
T:T IF A1=1 AND SL1 AND D1 = 1 then 1 else 0
V:V IF A1=1 AND AW1=1 AND F1 = 1 then 1 else 0
W:W IF A1=1 AND AL1=1 AND F1 = 1 then 1 else 0
Y:Y IF A1=1 AND AW1=1 AND D1 = 1 then 1 else 0
Z:Z IF A1=1 AND AL1 AND D1 = 1 then 1 else 0


I hope someone can help me with this....

 

[Domenic]

Based on the new layout, try the following for the first set of criteria...

=SUM(IF(Sheet2!$D$2:$D$100=$A8,IF((Sheet2!$I$2:$I$100)*(Sheet2!$K$2:$K$100)*(Sheet2!$R$2:$R$100),1)))

...confirmed with CONTROL+SHIFT+ENTER. Then the references for the test columns will have to be changed for each new set of criteria. However, you may prefer to do the following instead...

Let C5:C7 contain H1, SW1, and F1, for the first set of criteria

Let D5:D7 contain H1, SL1, and F1, for the second set of criteria

Continue to do the same for the other sets of criteria. Then, enter the following formula in C8, copy down the column, and to other target columns...

=SUM(IF(Sheet2!$D$2:$D$100=$A8,IF(MMULT(IF(ISNUMBER(MATCH(Sheet2!$I$1:$S$1,C$5:C$7,0)),Sheet2!$I$2:$S$10,0),TRANSPOSE(COLUMN(Sheet2!$I$2:$S$10)^0))=3,1)))

Note that both these formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Excel will automatically place braces {...} around the formula indicating that the formula has been entered correctly.

Hope this helps!