Skip blank rows and certain rows

gomes123

New Member
Joined
Jun 16, 2021
Messages
1
I have some data in one column (column B) as follows:

Fruits
Apple
Orange
Strawberry

Snacks
Chocolate
Candy
Ice cream

Vegetables
Spinach
Beans

-------------------------------------------

How would I do a numbering in column A that would exclude all blank cells and the text such as Fruits or Snacks Or Vegetables?

I would like it to look like this:

Fruits
1 Apple
2 Orange
3 Strawberry


Snacks
4 Chocolate
5 Candy
6 Ice cream

Vegetables
7 Spinach
8 Beans


I tried something like the formula below, and dragging it down, but its not working

Code:
=IF(OR(A11<>"",A11<>"Fruits"),COUNTA($A$11:A11),"")
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,068
Office Version
  1. 365
Platform
  1. Windows
Put this in cell A2 and copy down:
Excel Formula:
=IF(OR(B1="",B2=""),"",MAX(A$1:A1)+1)
 

Forum statistics

Threads
1,141,606
Messages
5,707,360
Members
421,503
Latest member
Rickys03

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
Top