CountIFs with multiple criteria and duplication

[sammipd]

Within a larger spreadsheet I have a Type (alpha, beta) column and Code 1 and Code 2 columns. Code columns are numeric from 1.1-7.9. I need to determine the number of alpha's who are coded 5.0-5.9 in either column without duplication. Some alphas are coded 5.0-5.9 in both columns but should only be counted here once. There was a similar thread earlier today and I couldn’t tweak the responses to fit this problem. Can someone help?

 

[Marcelo Branco]

A small data sample (~10 rows) along with expected result(s) would be helpful.

M.

 

[sammipd]

The question is how many Alpha's are coded in the 5.0-5.9 range. The answer should be 4 (lines 1,2,4,6).
Although lines 4 and 6 have 2 codes in the proper range, they should only be counted once. How can I make this work?

	Type	Code1	Code2
1	Alpha	5.7	3.2
2	Alpha	1.4	5.9
3	Beta	5.4	3.2
4	Alpha	5.3	5.8
5	Beta	6.8	5.2
6	Alpha	5.3	5.6
7	Beta	5.3	2.5
8	Alpha	3.4	6.5

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

 

[Eric W]

If your Code 1 and Code 2 columns are adjacent, you can use the I2 formula. If not, the J2 formula should work.

	A	B	C	D	E	F	G	H	I	J
1		Type	Code 1	Code 2		Type	Lower limit	Upper Limit	Unique Count
2	1	Alpha	5.7	3.2		alpha	5	5.9	4	4
3	2	Alpha	1.4	5.9
4	3	Beta	5.4	3.2
5	4	Alpha	5.3	5.8
6	5	Beta	6.8	5.2
7	6	Alpha	5.3	5.6
8	7	Beta	5.3	2.5
9	8	Alpha	3.4	6.5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet16​

Array Formulas Cell Formula I2 {=SUM(SIGN(MMULT(IF(($C$2:$D$9>=G2)*($C$2:$D$9<=H2)*($B$2:$B$9=F2),1,0),{1;1})))} J2 {=SUM(IF((($C$2:$C$9>=G2)*($C$2:$C$9<=H2)+($D$2:$D$9>=G2)*($D$2:$D$9<=H2))*($B$2:$B$9=F2),1))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Both are array formulas. Also, you can incorporate the F2:H2 values in the formula itself if you like.

 

[sammipd]

J2 worked. Thank you!
I need to learn more about arrays.