Thanks:  0
Likes:  0

1. ## Category Formula Help

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!

 A B C D E F G 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 18 29

2. ## Re: Category Formula Help

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

3. ## Re: Category Formula Help

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.

Thank you again for your help! I appreciate it greatly!!!

4. ## Re: Category Formula Help

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

Worksheet Formulas
CellFormula
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)),"")

Array Formulas
CellFormula
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))}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

Workbook Defined Names
NameRefers To
o='Convert Amount to words'!\$I\$5:\$AB\$5
T='Convert Amount to words'!\$I\$7:\$R\$7

5. ## Re: Category Formula Help

Hi Kiyo052,

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

And now, look at this:

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

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

6. ## Re: Category Formula Help

I've attached the workbook of what I'm trying to accomplish. Thanks in advance to all those who can help and a special thanks to shyy and markmzz for the help thus far!!

http://tinyurl.com/pvvlxm2

7. ## Re: Category Formula Help

Originally Posted by kiyo052
I've attached the workbook of what I'm trying to accomplish. Thanks in advance to all those who can help and a special thanks to shyy and markmzz for the help thus far!!

http://tinyurl.com/pvvlxm2

Markmzz

8. ## Re: Category Formula Help

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

9. ## Re: Category Formula Help

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

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

10. ## Re: Category Formula Help

It works great!!!

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

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•