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!!


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 74"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1_hypothesis[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<colgroup><col style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]1_for/against[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 69"]
<colgroup><col style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]1_thick/thin[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 74"]
<colgroup><col style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]2_hypothesis[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 75"]
<colgroup><col style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]2_for/against[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 69"]
<colgroup><col style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]2_thick/thin[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 68"]
<colgroup><col style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]Sector[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]Thin[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]Thin[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]Thin[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]CF[/TD]
[TD="align: center"]Thin[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]Thick[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]Thin[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]Thin[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]CF[/TD]
[TD="align: center"]Thick[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]Thick[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]Thick[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]Thick[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]Thick[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[/TR]
</tbody>[/TABLE]

 

[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!