autonumber in specific column with skip aggregate row

[KalilMe]

hello
I need auto numbering in specific column with skip any word=AGGREGAT" it's existed in col B so I put some numbers what should be in COL B

REPORT1.xlsx
	A	B	C	D	E	F	G	H
2	ITEM	GOODS	TYPE	PR	PU	SA	BL	PU
3	1	FR	BANANA	SO	380	200	180	19
4	2	FR	APPLE	TU	120	40	80	23
5	3	FR	APPLE	IT	120	-	120	100
6		AGGREGATE			620	240		142
7	1	VEG	TOMATO	EG	80	10	70	234
8	2	VEG	POTATO	GR	100	10	90	123
9	3	VEG	ONION	PO	120	10	110	123
10	4	VEG	ONION	LOC	101		101	156
11		AGGREGATE			401	30		636
12	1	FO1	TUNE160G	SP	200	20	180	56
13	2	FO1	TUNE160G	IN	140		140	64
14	3	FO2	TUNE180G	TH	300	30	270	22
15	4	FO2	TUNE180G	TW	130		130	11
16	5	FO3	CHEESE CHEEDER	NE	250		250	123
17	6	FO3	CHEESE MOZZIRELA	IT	300		300	123
18	7	FO5	BISCUITS SW	SW	150		150	34
19	8	FO5	BISCUITS SW	SS	250		250	15
20		AGGREGATE			1,720	50		448
DATA

Cell Formulas
Range	Formula
G12:G19,G7:G10,G3:G5	G3	=E3-F3
H6,E6:F6	E6	=SUM(E3:E5)
H11,E11:F11	E11	=SUM(E7:E10)
H20,E20:F20	E20	=SUM(E12:E19)

 

[Oaktree]

Try:

A3 =IF(OR(A2="",A2="Item"),1,IF(B3="Aggregate","",A2+(B3<>"Aggregate")))

copied down.

 

[KalilMe]

it doesn't work at all don't give me any error and it doesn't seem to be formula , it shows like any value put in the cell

 

[Peter_SSs]

it doesn't work at all don't give me any error and it doesn't seem to be formula , it shows like any value put in the cell

I suspect that you may have misinterpreted what Oaktree posted. The suggested formula starts at the first "=" sign and the A3 before that was telling you where to put the formula.
Here it is working.

21 05 08.xlsm
	A	B
2	ITEM	GOODS
3	1	FR
4	2	FR
5	3	FR
6		AGGREGATE
7	1	VEG
8	2	VEG
9	3	VEG
10	4	VEG
11		AGGREGATE
12	1	FO1
13	2	FO1
14	3	FO2
15	4	FO2
16	5	FO3
17	6	FO3
18	7	FO5
19	8	FO5
20		AGGREGATE
Numbering

Cell Formulas
Range	Formula
A3:A19	A3	=IF(OR(A2="",A2="Item"),1,IF(B3="Aggregate","",A2+(B3<>"Aggregate")))

However, here is another option that you might consider.

21 05 08.xlsm
	A	B
2	ITEM	GOODS
3	1	FR
4	2	FR
5	3	FR
6		AGGREGATE
7	1	VEG
8	2	VEG
9	3	VEG
10	4	VEG
11		AGGREGATE
12	1	FO1
13	2	FO1
14	3	FO2
15	4	FO2
16	5	FO3
17	6	FO3
18	7	FO5
19	8	FO5
20		AGGREGATE
Numbering (2)

Cell Formulas
Range	Formula
A3:A19	A3	=IF(B3="AGGREGATE","",N(A2)+1)

 

[KalilMe]

I suspect that you may have misinterpreted what Oaktree posted. The suggested formula starts at the first "=" sign and the A3 before that was telling you where to put the formula.

I know about A3 it shouldn't put in the cell with the formula
the problem seems from the file I tested for another file it works and return testing my original file it works very well
very strange ????
thanks guys for your formulas
just curiosity , how can I do that like this?

1.xlsm
	A	B	C	D	E	F
1	ITEM		DESCRIBE			Q1
2		GOODS	TYPE	PR	PU	SA
3	1	FR	BANANA	SO	380	200
4	2	FR	APPLE	TU	120	40
5	3	FR	APPLE	IT	120	-
6		AGGREGATE			620	240
7	4	VEG	TOMATO	EG	80	10
8	5	VEG	POTATO	GR	100	10
9	6	VEG	ONION	PO	120	10
10	7	VEG	ONION	LOC	101
11		AGGREGATE			401	30
12	8	FO1	TUNE160G	SP	200	20
13	9	FO1	TUNE160G	IN	140
14	10	FO2	TUNE180G	TH	300	30
15	11	FO2	TUNE180G	TW	130
16	12	FO3	CHEESE CHEEDER	NE	250
17	13	FO3	CHEESE MOZZIRELA	IT	300
18	14	FO5	BISCUITS SW	SW	150
19	15	FO5	BISCUITS SW	SS	250
20		AGGREGATE			1,720	50
DATA

Cell Formulas
Range	Formula
E6:F6	E6	=SUM(E3:E5)
E11:F11	E11	=SUM(E7:E10)
E20:F20	E20	=SUM(E12:E19)

 

[Peter_SSs]

just curiosity , how can I do that like this?

Here is one way

21 05 08.xlsm
	A	B
1	ITEM
2		GOODS
3	1	FR
4	2	FR
5	3	FR
6		AGGREGATE
7	4	VEG
8	5	VEG
9	6	VEG
10	7	VEG
11		AGGREGATE
12	8	FO1
13	9	FO1
14	10	FO2
15	11	FO2
16	12	FO3
17	13	FO3
18	14	FO5
19	15	FO5
20		AGGREGATE
Numbering (3)

Cell Formulas
Range	Formula
A3:A19	A3	=IF(B3="AGGREGATE","",MAX(A$2:A2)+1)

 

[KalilMe]

that's a great ! many thanks for your help

 

[Peter_SSs]

You're welcome. Glad we could help.