Category Formula Help

kiyo052

Board Regular
Joined
Oct 11, 2013
Messages
134
Excel 2010

Is there a way to build a formula for E4, F4, E5, and F5?

My data is going to be in columns A, B, C. Is there a formula that would categorize the data into E and F? Please let me know! Thanks in advance!

ABCDEFG
1
2
3ID number OptionsCategory
41005x7RugsRugs3
51005x8RugsBed2
61005x9Rugs
72005x7Rugs
82005x8Rugs
92005x9Rugs
103005x7Rugs
113005x8Rugs
123005x9Rugs
13400QueenBed
14400KingBed
15400TwinBed
16500FullBed
17500KingBed
18
29

<tbody>
</tbody>
 
Last edited:

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Maybe this:

Code:
In E4

=IFERROR(INDEX($C$4:$C$17,MATCH(0,INDEX(COUNTIF($E$3:$E3,$C$4:$C$17),,),0)),"")

In F4 - array formula - use Ctrl+Shift+Enter and not only Enter to enter the formula

=SUM(--(FREQUENCY(IF($C$4:$C$17=$E4,$A$4:$A$17),IF($C$4:$C$17=$E4,$A$4:$A$17))>0))

Markmzz
 
Upvote 0
in F4 it is reading as 0 when I used the formula. Also, when I drag down E3, it is still reading Rugs and not bed.

2aou9j.png


Thank you again for your help! I appreciate it greatly!!!
 
Last edited:
Upvote 0
Your data is not in cell E4, formula is trying to pick up the value in E4. Like what you stated, put it in E4 and F4


Excel 2010
ABCDEF
1
2
3ID numberOptionsCategory
41005x7RugsRugs3
51005x8RugsBed2
61005x9Rugs 
72005x7Rugs
82005x8Rugs
92005x9Rugs
103005x7Rugs
113005x8Rugs
123005x9Rugs
13400QueenBed
14400KingBed
15400TwinBed
16500FullBed
17500KingBed
Categoizing
Cell Formulas
RangeFormula
E4=IFERROR(INDEX($C$4:$C$17,MATCH(0,INDEX(COUNTIF($E$3:$E3,$C$4:$C$17),,),0)),"")
E5=IFERROR(INDEX($C$4:$C$17,MATCH(0,INDEX(COUNTIF($E$3:$E4,$C$4:$C$17),,),0)),"")
E6=IFERROR(INDEX($C$4:$C$17,MATCH(0,INDEX(COUNTIF($E$3:$E5,$C$4:$C$17),,),0)),"")
F4{=SUM(--(FREQUENCY(IF($C$4:$C$17=$E4,$A$4:$A$17),IF($C$4:$C$17=$E4,$A$4:$A$17))>0))}
F5{=SUM(--(FREQUENCY(IF($C$4:$C$17=$E5,$A$4:$A$17),IF($C$4:$C$17=$E5,$A$4:$A$17))>0))}
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
o='Convert Amount to words'!$I$5:$AB$5
T='Convert Amount to words'!$I$7:$R$7
 
Upvote 0
Hi Kiyo052,

Shyy is right (thanks Shyy). The formulas are in the wrong place (look at your post #1).

And now, look at this:

<form name="vbform" class="block vbform" style="margin: auto auto 1em;" action="newreply.php?do=postreply&t=735831" method="post"></form>A
B
C
D
E
F
1
2
3
ID number
Options
Category
4
100
5x7
Rugs
Rugs
3
5
100
5x8
Rugs
Bed
2
6
100
5x9
Rugs
7
200
5x7
Rugs
8
200
5x8
Rugs
9
200
5x9
Rugs
10
300
5x7
Rugs
11
300
5x8
Rugs
12
300
5x9
Rugs
13
400
Queen
Bed
14
400
King
Bed
15
400
Twin
Bed
16
500
Full
Bed
17
500
King
Bed
****
*********
********
********
**
******
*****

<tbody>
</tbody>


Formulas

Code:
In E4

=IFERROR(INDEX($C$4:$C$17,MATCH(0,INDEX(COUNTIF($E$3:$E3,$C$4:$C$17),,),0)),"")

In F4 - array formula - use Ctrl+Shift+Enter and not only Enter to enter the formula

=SUM(--(FREQUENCY(IF($C$4:$C$17=$E4,$A$4:$A$17),IF($C$4:$C$17=$E4,$A$4:$A$17))>0))


Markmzz
 
Upvote 0
Here is the image if anyone can't download the work book. Thanks again to all those who can help!

ofu8ea.png
 
Upvote 0
Maybe this:

Layout

A
B
C
D
E
F
G
1
2
3
ID number
Options
Category
Top Categorias
ID Count
Group Count
4
100
5x7
Rugs
Rugs
6
2
5
100
5x8
Rugs
Bed
5
2
6
100
5x9
Rugs
Pillow
3
1
7
200
5x7
Rugs
8
200
5x8
Rugs
9
200
5x9
Rugs
10
300
5x7
Pillow
11
300
5x8
Pillow
12
300
5x9
Pillow
13
400
Queen
Bed
14
400
King
Bed
15
400
Twin
Bed
16
500
Full
Bed
17
500
King
Bed
****
*********
********
********
**
***************
*********
************

<tbody>
</tbody>


Formulas

Code:
In E4 - array formula - use Ctrl+Shift+Enter and not only Enter to enter the formula

=IFERROR(INDEX($C$4:$C$17,MATCH(LARGE(IF(COUNTIF($E$3:$E3,$C$4:$C$17)=0,COUNTIF($C$4:$C$17,$C$4:$C$17)),1),
COUNTIF($C$4:$C$17,$C$4:$C$17)*(COUNTIF($E$3:$E3,$C$4:$C$17)=0),0)),"")

In F4 - use only Enter

=IF(E4="","",COUNTIF($C$4:$C$17,$E4))

In G4 - array formula - use Ctrl+Shift+Enter and not only Enter to enter the formula

=IF(E4="","",SUM(--(FREQUENCY(IF($C$4:$C$17=$E4,$A$4:$A$17),IF($C$4:$C$17=$E4,$A$4:$A$17))>0)))


Markmzz
 
Last edited:
Upvote 0
It works great!!!

The only problem at this point is when I tried to add a title and description Row G pulls an error

2lk740w.png


I'm so close!! Thank you for your help thus far!!!!!
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top