Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: Category Formula Help

  1. #1
    Board Regular
    Join Date
    Oct 2013
    Posts
    134
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Last edited by kiyo052; Oct 29th, 2013 at 02:04 PM.

  2. #2
    MrExcel MVP
    Join Date
    May 2011
    Posts
    3,699
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default 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. #3
    Board Regular
    Join Date
    Oct 2013
    Posts
    134
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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!!!
    Last edited by kiyo052; Oct 30th, 2013 at 11:42 AM.

  4. #4
    Board Regular
    Join Date
    Nov 2008
    Posts
    1,484
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #5
    MrExcel MVP
    Join Date
    May 2011
    Posts
    3,699
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default 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. #6
    Board Regular
    Join Date
    Oct 2013
    Posts
    134
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #7
    MrExcel MVP
    Join Date
    May 2011
    Posts
    3,699
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Category Formula Help

    Quote Originally Posted by kiyo052 View Post
    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
    Look at your PM Box.

    Markmzz

  8. #8
    Board Regular
    Join Date
    Oct 2013
    Posts
    134
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #9
    MrExcel MVP
    Join Date
    May 2011
    Posts
    3,699
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default 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
    Last edited by markmzz; Oct 30th, 2013 at 06:29 PM.

  10. #10
    Board Regular
    Join Date
    Oct 2013
    Posts
    134
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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