# Condition Count Formula Related Query

#### xlmaniac

##### Well-known Member
Dear All,
I do have the following data set across C6:D15.
I would like to get my desired result as mentioned in the cells across D2:D3.
My present formula is not yielding the desired result.
Could somebody help in getting this fixed?
Regards
Sheet1

 * B C D 1 * Result With My Formula Desired Result 2 No Of Styles 3 3 3 No Of Colors 3 9 4 * * * 5 * * * 6 * Style Color 7 * Bootom1 White 8 * Bootom1 Yellow 9 * Bootom1 Blue 10 * Bootom2 White 11 * Bootom2 Yellow 12 * Bootom3 Blue 13 * Bootom3 White 14 * Bootom3 Yellow 15 * Bootom3 Blue

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 81px;"><col style="width: 151px;"><col style="width: 93px;"></colgroup><tbody>
</tbody>

 Cell Formula C2 {=SUM(IF(FREQUENCY(IF(C7:C15<>"",MATCH("~"&C7:C15,C7:C15&"",0)),ROW(\$C\$7:\$C\$15)-ROW(C7)+1),1))} C3 {=SUM(IF(FREQUENCY(IF(D7:D15<>"",MATCH("~"&D7:D15,D7:D15&"",0)),ROW(\$D\$7:\$D\$15)-ROW(D7)+1),1))}

<tbody>
</tbody>
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
If you're asking for the number of unique style/color combinations, it would seem the answer in D3 should be 8, not 9. If that's not the case, please explain how you get 9. Otherwise,

Here's a shorter, non-array formula for the number of styles in C2:
=SUMPRODUCT(1/COUNTIF(C7:C15,C7:C15))

Here's a formula for the number of unique style/color combinations in C3:
=SUMPRODUCT(1/COUNTIFS(D7:D15,D7:D15,C7:C15,C7:C15))

Replies
6
Views
140
Replies
4
Views
144
Replies
11
Views
467
Replies
7
Views
574
Replies
0
Views
452

1,203,241
Messages
6,054,320
Members
444,717
Latest member
melindanegron

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