Count / Frequency

[Tinä]

Hi,

I have a Dynamic Named Range called ROOM_TYPE which consists of tex
based names -

Example:
Executive, Admin, Manager, Trainees, Graphics.

The room names appear in a single column defined as ROOM_TYPE.


Row No ROOM_TYPE Count
ROW5 Executive 1
ROW6 Executive 2
ROW7 Executive 3
ROW8 Admin 1
ROW9 Manager 1
ROW10 Admin 2
ROW11 Graphics 1
ROW12 Manager 2
ROW13 Trainees 1
ROW14 Graphics 2
ROW15 Manager 3

Require individual (sequential) count/ frequency that increments eac
time the same Room type appears in the list.

This data will have filters applied, so the counting process shoul
provide a sequential frequency count of Filtered Visible Cells as wel
as working with non-filtered cells.

Thanks
Tin

 

[duane]

the top row gets a 1 in column B

then place this in the 2nd row in column B

(this assumes the top row is 5)

=COUNTIF(A5:$A$5,A6)+1

copy down column B

for variable length range you could have a macro insert the above fo
the correct row

 

[Aladin Akyurek]

What follows is a bit heavy...

In B5 enter & copy down:

=SUMPRODUCT(SUBTOTAL(3,OFFSET($A$5:A5,ROW($A$5:A5)-MIN(ROW($A$5:A5)),,1)),--($A$5:A5=A5))

In A3 enter:

=IF(SUBTOTAL(3,A5:A16)<>COUNTA(A5:A16),MAX(IF(SUBTOTAL(3,OFFSET(A5:A16,ROW(A5:A16)-MIN(ROW(A5:A16)),,1)),B5:B16)),"")

which must be confirmed with control+shift+enter instead of just wit
enter.

Note 1. I used a bit larger range than your sample range for testin
purposes. So, just adjust the ranges to suit.

Note 2. The formula in A3 tests the range in A while it calculates th
max value in B when the autofilter is activated by a choice in A. Th
reason why the B range is not part of testing or why the simple
SUBTOTAL(4,B-range) is not used, is due to a glitch in OFFSET, no
worth discussing here.

Note 3. If you're on Excel 2003, you could turn the data area into
LIST by activating Data|List|Create List. However, this would require
different formula in B4.