## SUBTOTAL Second Count / sub-count of Filtered Visible Cells

### SUBTOTAL Second Count / sub-count of Filtered Visible Cells

Hi Excel Forum,

original ThreadID
( http://www.yqcomputer.com/ )

I have tried a Pivot Table as was previously suggested but the result
included the Filtered Hidden Rows as well as the Filtered Visible Rows
I require only the data of the Filtered Visible Rows. The Filtered dat
will be constantly changing and a solution that can be performe
on-the-fly would be more suitable.

Is there any way of providing a solution using Formula or VBA to coun
the individual departments and then count the subset of department
within the Filtered Visible Rows to ultimately provide a summary coun
for each department?

Is it possible to get a second count (sub-count) of Filtered Visibl
Cells summarising and distinguishing the different departments liste
below using SUBTOTAL,3 and COUNTIF in a Formula? Will COUNTIF work o
Filtered Visible Cells?

Example:
Column P has non-numeric Filtered data:
Departments:
103/9
103/1
103/4
103/2
103/9
103/2
103/2
103/7
103/4
103/5
103/4
103/7
103/1
103/6
103/8
103/3
---------------------
16 TOTAL Count
---------------------

The ciriteria used will return a mix of departments and an individua
department may be listed more than once. The above Total Count of 16
gives a Count of all Filtered Visible Cells; i.e. departments. However
I would also like a summary Count of the departments.

If it is possible to summarise the Count of the above Filtered Visibl
departments using either Formula Functions or VBA - please advise wit
a working example. Example Summary Count of Departments -

Summary Data:
103/1 =2
103/2 =3
103/3 =1
103/4 =3
103/5 =1
103/6 =1
103/7 =2
103/8 =1
103/9 =2
-----------
16 TOTAL Count
-----------

Kind regards,
QT

### SUBTOTAL Second Count / sub-count of Filtered Visible Cells

Hi
if this is in column P try:
=SUMPRODUCT((\$P\$1:\$P\$1000="103/1")*(SUBTOTAL(3,OFFSET(\$P\$1,ROW(\$P\$1:\$P\$
10
00)-MIN(ROW(\$P\$1:\$P\$1000)),,))))

for the first department

results
count
count
Visible
with

### SUBTOTAL Second Count / sub-count of Filtered Visible Cells

Hi Frank,

Thank you very much for your assistance; the formula was just the righ
tonic......brilliant.

As an aside, I'm using Dynamic Name Ranges: is it possible to Referenc
the Offset cell with a Dynamic Named Range?

This is your working formula with my Dynamic Named Range:
=SUMPRODUCT((Dept="103/1")*(SUBTOTAL(3,OFFSET(\$P\$11,ROW(Dept)-MIN(ROW(Dept)),,))))

However, after the OFFSET function where I've got the A1-Style absolut
reference \$P\$11 can this reference be replaced with the Dynamic Name
Range "Dept" (SUBTOTAL(3,OFFSET(\$P\$11,

This is in the Define Name Refers To Box for name "Dept"
=OFFSET('Deptanalysis'!\$P\$9,2,0,COUNTA('Deptanalysis'\$P:\$P),1)

Kind regards
QT

### SUBTOTAL Second Count / sub-count of Filtered Visible Cells

Hi Frank,

Thank you very much for your assistance; the formula was just the righ
tonic......brilliant. However, could you possibly advise on my scenari
below:

quote:
--------------------------------------------------------------------------------
Originally posted by Frank Kabel
Hi
if this is in column P try:
=SUMPRODUCT((\$P\$1:\$P\$1000="103/1")*(SUBTOTAL(3,OFFSET(\$P\$1,ROW(\$P\$1:\$P\$1000)-MIN(ROW(\$P\$1:\$P\$1000)),,))))

for the first department
This is your working formula with my Dynamic Named Range:
=SUMPRODUCT((Dept="103/1")*(SUBTOTAL(3,OFFSET(\$P\$11,ROW(Dept)-MIN(ROW(Dept)),,))))

However, after the OFFSET function where I've got the A1-Style absolut
reference \$P\$11 can this reference be replaced with the Dynamic Name
Range "Dept" and if so, how? (SUBTOTAL(3,OFFSET(\$P\$11,

This is in the Define Name Refers To Box for name "Dept"
=OFFSET('Deptanalysis'!\$P\$9,2,0,COUNTA('Deptanalysis'\$P:\$P),1)

Kind regards
QT

### SUBTOTAL Second Count / sub-count of Filtered Visible Cells

Hi
try
=SUMPRODUCT((Dept="103/1")*(SUBTOTAL(3,OFFSET(INDEX(Dept,1,1),ROW(Dept)
-MIN(ROW(Dept)),,))))

-----------
=SUMPRODUCT((\$P\$1:\$P\$1000="103/1")*(SUBTOTAL(3,OFFSET(\$P\$1,ROW(\$P\$1:\$P\$
1000)-MIN(ROW(\$P\$1:\$P\$1000)),,))))

### SUBTOTAL Second Count / sub-count of Filtered Visible Cells

Hi Frank,

Formula works beautifully. Thank you once again for all your help: ver
much appreciated.

Kind regards
QT

### SUBTOTAL Second Count / sub-count of Filtered Visible Cells

Hi Frank & Excel Forum,

I've tried to adapt your formula above to work with a similar column o
another worksheet; the main difference being the value in the cells ar
formula which display the relevant text "103/1". I've tried to use th
INDIRECT and ADDRESS functions unsuccessfully.

Could you enlighten me as to how I can amend the formula above t
provide the same calculation on cells where the value is a formula bu
it displays TEXT (103/1).

Kind regards
QT

### SUBTOTAL Second Count / sub-count of Filtered Visible Cells

> Hi Frank & Excel Forum,

---------
=SUMPRODUCT((Dept="103/1")*(SUBTOTAL(3,OFFSET(INDEX(Dept,1,1),ROW(Dept)
-MIN(ROW(Dept)),,))))
---------
but

Hi
normally no need to change the formula. But could you post your formula
which produces the "103/1" value. Maybe there're some spaces or it is
in fact only a number formated with a slash?

Frank

### SUBTOTAL Second Count / sub-count of Filtered Visible Cells

Hi Frank,

Your working formula previously used (no underlying formulae in cell
concerned):
=SUMPRODUCT((Dept="103/1")*(SUBTOTAL(3,OFFSET(INDEX(Dept,1,1),ROW(Dept)-MIN(ROW(Dept)),,))))

The formula producing the 103/1 is:
=LOOKUP(\$K13,\$AF\$4:\$AF\$17,\$AG\$4:\$AG\$17)
this cell is formatted using General Number Format.

The Lookup value cell \$K13 is formatted using General Number Format bu
refers to a cell =\$I14 that also uses General Number Format, this cel
\$I14 is the one that LOOKUP uses as the LOOKUP_Value.
LOOKUP_Vector is formatted using General Number Format.
LOOKUP_Result is formatted using Text Format, this returns the 103/1.

However, as mentioned above the LOOKUP_Result is returned to a cel
with the underlying =LOOKUP formula and this cell is formatted a
General Number Format because of the formula in it, although i
displays 103/1.

I hope you are able to make some sense of this.

Kind regards
QT

### SUBTOTAL Second Count / sub-count of Filtered Visible Cells

Hi Frank,

Your working formula previously used (I had no underlying formulae in
cells concerned):
=SUMPRODUCT((Dept="103/1")*(SUBTOTAL(3,OFFSET(INDEX(Dept,1,1),ROW(Dept)-MIN(ROW(Dept)),,))))

quote:
--------------------------------------------------------------------------------
Originally posted by Frank Kabel
Hi
normally no need to change the formula. But could you post your formula
which produces the "103/1" value. Maybe there're some spaces or it is in
fact only a number formated with a slash?
Frank
--------------------------------------------------------------------------------

The formula producing the 103/1 is:
=LOOKUP(\$K13,\$AF\$4:\$AF\$17,\$AG\$4:\$AG\$17)
this cell is formatted using General Number Format.

The Lookup value cell \$K13 is formatted using General Number Format but
refers to a cell =\$I14 that also uses General Number Format, this cell
\$I14 is the one that LOOKUP uses as the LOOKUP_Value.
LOOKUP_Vector is formatted using General Number Format.
LOOKUP_Result is formatted using Text Format, this returns the 103/1.

However, as mentioned above the LOOKUP_Result is returned to a cell
with the underlying =LOOKUP formula and this cell is formatted as
General Number Format because of the formula in it, although it
displays 103/1.

I hope you are able to make some sense of this.

Kind regards
QTE

---
Message posted from http://www.yqcomputer.com/

### SUBTOTAL Second Count / sub-count of Filtered Visible Cells

Hi
if you like email me your file :-)

=SUMPRODUCT((Dept="103/1")*(SUBTOTAL(3,OFFSET(INDEX(Dept,1,1),ROW(Dept)
-MIN(ROW(Dept)),,))))
-----------
-----------

### SUBTOTAL Second Count / sub-count of Filtered Visible Cells

Hi Frank,

Unfortunately, will not give permission for file to be e-mailed.
Any other solutions I can try to resolve this would be appreciated.

Kind regards
QT

### SUBTOTAL Second Count / sub-count of Filtered Visible Cells

Hi Frank,

Any other suggestions I can try to resolve this would be appreciated.

Kind regards,
QT

### SUBTOTAL Second Count / sub-count of Filtered Visible Cells

Hi
the problem seems to lay in the format/strucutre/etc of your data. I
can fully understand that you're not allowed to email sensible
information to someone outside of your company.
But maybe you can just set-up a small sample file which also contains
your error and which does NOT contain any sensible information