Frequency/Match formula help

[freil]

Hi There,

I'm a relative novice at Excel and I'm having trouble working out a formula.

I'm trying to count the unique numbers in ROW G (Sectors) if certain criteria are met in other cells. For example,
IF (1_hypothesis = 9) AND (1_for/against = D OR 1_for/against = CF) AND (1_thick/thin = Thick) AND (2_hypothesis = 9) AND (2_for/against = D OR 2_for/against = CF) AND (2_thick/thin = Thick), I want it to count the number of unique numbers in Sectors (Row G).

I've tried SUM (with FREQUENCY, MATCH) and I get mostly there, but it stops working when I add more criteria.

If anyone could provide me with the formula, that would be great.

Thanks so much!!

	A	B	C	D	E	F	G
1	1_hypothesis <colgroup><col></colgroup><tbody> </tbody>	1_for/against <colgroup><col style="text-align: center;"></colgroup><tbody> </tbody>	1_thick/thin <colgroup><col style="text-align: center;"></colgroup><tbody> </tbody>	2_hypothesis <colgroup><col style="text-align: center;"></colgroup><tbody> </tbody>	2_for/against <colgroup><col style="text-align: center;"></colgroup><tbody> </tbody>	2_thick/thin <colgroup><col style="text-align: center;"></colgroup><tbody> </tbody>	Sector <colgroup><col style="text-align: center;"></colgroup><tbody> </tbody>
2	9	D	Thin	3	O	Thin	1
3	10	D	Thin	9	CF	Thin	1
4	9	O	Thick				1
5	3	D	Thin	9	D	Thin	2
6	9	CF	Thick				1
7	1	D	Thick	2	D	Thick	3
8	9	D	Thick				1
9	9	D	Thick				2

<tbody>
</tbody>

 

[rilzniak]

=SUM(IF(FREQUENCY(IF($A$2:$A$9=9,IF(OR($B$2:$B$9="D",$B$2:$B$9="CF"),IF($C$2:$C$9="Thick",IF($D$2:$D$9=9,IF(OR($E$2:$E$9="D",$E$2:$E$9="CF"),IF($F$2:$F$9="Thick",$G$2:$G$9)))))),$G$2:$G$9)>0,1))

Confirmed with Control+Shift+Enter

 

[freil]

Thanks rilzniak!

I get a 0, but I suspect it's because I had a typo above (correction in RED).

IF (1_hypothesis = 9) AND (1_for/against = D OR 1_for/against = CF) AND (1_thick/thin = Thick) OR (2_hypothesis = 9) AND (2_for/against = D OR 2_for/against = CF) AND (2_thick/thin = Thick)

I tried to fix the formula myself, but I'm so bad at this, I couldn't figure it out!

 

[rilzniak]

=SUM(IF(FREQUENCY(IF($A$2:$A$9=9,IF(OR($B$2:$B$9="D",$B$2:$B$9="CF"),IF(OR($C$2:$C$9="Thick",$D$2:$D$9=9),IF(OR($E$2:$E$9="D",$E$2:$E$9="CF"),IF($F$2:$F$9="Thick",$G$2:$G$9))))),$G$2:$G$9)>0,1))

Confirmed with Control+Shift+Enter

 

[freil]

I'm getting a 0. I should be getting 2.

 

[rilzniak]

Based on your initial table, the criteria is correct. Suggest you carefully review the criteria you want as rows 6, 8, and 9 will return a no results since columns D, E, and F are blank (I'm assuming you believe those rows should result in a count of two).

 

[freil]

Rows 5, 6, 8, 9 meet the criteria I'm look for.

Row 5 = 9, D, Thick (Columns D, E, F), Column G=2
Row 6 = 9, CF, Thick (Columns A, B, C), Column G=1
Row 8 = 9, D, Thick (Columns A, B, C), Column G=1
Row 9 = 9, D, Thick (Columns A, B, C), Column G=2

There are 4 instances that meet my criteria, but there are only 2 unique values within those instances when calculating column G.

Perhaps I wasn't explaining my requirements very well (chalk it up to being a newbie!).

When I input the formula, I simply get 0.

 

[rilzniak]

Looking at your data table:

Row 5 = 3 - D - Thin - 9 - D - Thin --->Does not meet conditions
Row 6 = 9 - CF - Thick - *blank* - *blank* - *blank* --->Does not meet conditions
Row 8 = 9 - D - Thick - *blank* - *blank* - *blank* --->Does not meet conditions
Row 9 = 9 - D - Thick - *blank* - *blank* - *blank* --->Does not meet conditions

Required Conditions as described:

Column A = 9
Column B = D or CF
Column C = Thick OR Column D = 9
Column E = D or CF
Column F = Thick

All of those conditions must be met for it to count the unique values in column G.

Examples which WOULD meet criteria:

9 - D - Thin - 9 - D - Thick
9 - CF - Thick - 5 - D - Thick

It sounds like you need to revise your criteria so the formula will match what you're looking for.

 

[freil]

Hi,

I'm looking for a formula that will count the UNIQUE values in Column G with the following criteria:

[IF (1_hypothesis = 9) AND (1_for/against = D OR 1_for/against = CF) AND (1_thick/thin = Thick)]

OR

[(2_hypothesis = 9) AND (2_for/against = D OR 2_for/against = CF) AND (2_thick/thin = Thick)]

Thanks!