# Frequency/Match formula help

#### freil

##### New Member
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!!

ABCDEFG
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>
29DThin3OThin1
310DThin9CFThin1
49OThick1
53DThin9DThin2
69CFThick1
71DThick2DThick3
89DThick1
99DThick2

<tbody>
</tbody>

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

#### rilzniak

##### Active Member
=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

##### New Member
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

##### Active Member
=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

##### New Member
I'm getting a 0. I should be getting 2.

#### rilzniak

##### Active Member
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

##### New Member
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

##### Active Member

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

##### New Member
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!

Replies
2
Views
181
Replies
9
Views
259
Replies
5
Views
177
Replies
3
Views
103
Replies
0
Views
485

1,191,219
Messages
5,985,328
Members
439,958
Latest member
qb0000

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back