If Then formula

[carrera]

I have a sheet that I auto filter by job title, i.e. Doctor, Nurse, Tech,
Admin.

With no auto filter on, the each person list listed by name, with the column
next to their name designating them as 1 through 100.

If I auto filter for, let's say Nurse, of which there are 35, what formula
can I put in the cell to the right of the name to number these people as 1
through 35?

In other words, the number would change depending on which rows were visible
at the time.

The names on this list change on a regular basis.

 

[Sandy Mann]

This may not be what you want.

Apply a filter for say Nurses. In cell to the right of the 1st name - say
in row 2 enter 1 and in the cell under it enter the formula:

=A2+1

Now copy this formula down the visible cells using the fill handle.

Do the same with each job title. Copying down using the fill handle copies
only to visible cells so the formulas will only relate to the appropriate
job title.
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[carrera]

hmm...when I filter for nurses, the rows #'s are not going to be
sequential...for instance, the rows #'s read 5, 18, 24, 25, 32 etc...
so putting in A2 + 1 doesn't work, If I don't filter, and use that formula,
then filter, the numbers in the row are not sequential either.

I see what you are saying, but how do you get a formula to apply only to the
visible rows?

 

[BoniM]

=SUBTOTAL(103,$B$2:B2)

Enter this formula for the first person, where B is the column that contains
the name and the first person appears in row 2. Adjust as necessary. Copy
down for the entire list, will properly display current count when filtered...
Good luck!

 

[Sandy Mann]

Sorry, it seem to work for 20 odd rows then goes wrong and I only tested on
18 rows. Use BonM's formula.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[carrera]

Oh!
How stupid of me, that should have been obvious!
Thanks so much.

 

[Terry Tipsy]

Try the 'COUNTIF' function. On the test data I used, I inserted a row above
my filtered headings and entered this formula above the column I wanted to
count:
COUNTIF(F:F,F3)
This counted my filtered rows. Note: You must have the 'Iteration' button
selected at Tools/Options/Calculation or you will get the 'circular
reference' error.

Now that you have the rows counted, you can refer to this cell to create a
formula to number the displayed rows and fill down. In my case I put
'=$F$1-(F1-1)' in the first cell and then '=$F$1-($F$1-1)+O3' in the second
cell and thereafter. If desired, you would need to add to the formula to
check that the max count was not exceeded.

 

[Sandy Mann]

Using your idea:

=COUNTIF($A$2:$A$128,A2)-COUNTIF(A2:A128,A2)+1

Copied down all produces the numbers list when filtered.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk