Category Formula Help

[kiyo052]

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

<tbody>
</tbody>

 

[shyy]

paste the exact formula you are using

 

[kiyo052]

There seems to be random problems with the formula which is frustrating for me. When I re-did it it worked great!!! Then when I try to add any other data it freaks out. Then when I try pasting the exact data that worked... it didn't work anymore. here is the pic of what happened

Here is the pic of it working

Here is the pic of when I change column A a little bit

 

[shyy]

I am not entirely sure but I think FREQUENCY only works with numbers and not letters.

If that is right, you can fake the format to look like it has "GUC" but the cell value is really only numbers. Would you be okay with that?

You would have to use the custom number format and change for each different ID when required.

Under the custom number format put
"GUC"#

 

[kiyo052]

The thing is I'm going to have at least 500 items in my workbook all having different ID numbers and they all have random letters in it. Is there a way to format so that it would be able to read the numbers?

Sorry for the trouble! I really hope you can help!

 

[shyy]

This only takes into account if the ID has only 4 numbers, you need to adjust the 4 to whatever it is.

Excel 2010

	A	B	C	D	E	F	G
4	CUG1000		Rugs		Rugs		3
5	CUG1000		Rugs		Pillow		4
6	CUG3000		Rugs
7	CUG3000		Rugs
8	CUG2000		Rugs
9	CUG2000		Rugs
10	CUG2000		Rugs
11	ABC3000		Pillow
12	ABC3000		Pillow
13	ABC3100		Pillow
14	ABC3200		Pillow
15	ABC3300		Pillow
16	ABC3300		Pillow

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Array Formulas Cell Formula G4 {=IF($E4="","",SUM(--(FREQUENCY(IF(CATEGORY=$E4,--RIGHT(ID,4)),IF(CATEGORY=$E4,--RIGHT(ID,4)))>0)))} G5 {=IF($E5="","",SUM(--(FREQUENCY(IF(CATEGORY=$E5,--RIGHT(ID,4)),IF(CATEGORY=$E5,--RIGHT(ID,4)))>0)))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Workbook Defined Names Name Refers To CATEGORY =Sheet2!$C$4:$C$16 ID =Sheet2!$A$4:$A$16 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[markmzz]

Maybe this:

Array formula - use Ctrl+Shift+Enter and not only Enter to enter the formula

=SUM(IF(FREQUENCY(IF(CATEGORY=$E4,IF(ID<>"",MATCH("~"&ID,ID&"",0))),ROW(ID)-ROW(INDEX(ID,1,1))+1),1))

Markmzz

 

[shyy]

amazing,

what does MATCH("~"&ID,ID&"",0) & ROW(ID)-ROW(INDEX(ID,1,1))+1),1)) do?

 

[kiyo052]

PERFECT!!!!!! Right now it works like a charm. THANK YOU SO MUCH!! THE BOTH OF YOU!!!!


If anyone is reading this markmzz and shyy are the biggest help in the world!!!

 

[markmzz]

amazing,

what does MATCH("~"&ID,ID&"",0) & ROW(ID)-ROW(INDEX(ID,1,1))+1),1)) do?

Hi Shyy,

=MATCH("~"&ID,ID&"",0)

This part of the formula matches the ID range to ID range.
The "~" and "" are used to deal with special characters (like *)


ROW(ID)-ROW(INDEX(ID,1,1))+1

This part of the formula is a vector (from 1 to the last row in the range ID - like {1;2;3;4}).

In the formula, we have something like this:

=SUM(IF(FREQUENCY({1;1;1;4;4;4;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},{1;2;3;4;5;6;7;8;9;10;11;12;13;14}),1))

I hope that this help.

Markmzz

 

[markmzz]

PERFECT!!!!!! Right now it works like a charm. THANK YOU SO MUCH!! THE BOTH OF YOU!!!!


If anyone is reading this markmzz and shyy are the biggest help in the world!!!

You're welcome and thanks for the feedback.

Markmzz