SUBTOTAL Second Count / sub-count of Filtered Visible Cells

[QTE]

Hi Excel Forum,

original ThreadID
(http://www.excelforum.com//showthre...threadid=239331)

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

 

[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$
10
00)-MIN(ROW($P$1:$P$1000)),,))))

for the first department

 

[QTE]

Hi Frank,

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

*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

*[/QUOTE]
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

 

[QTE]

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[/QUOTE]
--------------------------------------------------------------------------------
As an aside, I'm using Dynamic Name Ranges: is it possible to Referenc
the Offset cell with a Dynamic Named Range that already has an existin
Offset determined in the Define Names Refer To Box?

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

 

[Frank Kabel]

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

 

[QTE]

Hi Frank,

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

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

*

Kind regards
QT

 

[QTE]

Hi Frank & Excel Forum,

*Hi Frank,

Formula works beautifully. Thank you once again for all your help
very much appreciated.
-----------------------------------------------------------------------------
Originally posted by Frank Kabel
Hi
try
=SUMPRODUCT((Dept="103/1")*(SUBTOTAL(3,OFFSET(INDEX(Dept,1,1),ROW(Dept)-MIN(ROW(Dept)),,))))

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

 

[Frank Kabel]

Hi Frank & Excel Forum,

=SUMPRODUCT((Dept="103/1")*(SUBTOTAL(3,OFFSET(INDEX(Dept,1,1),ROW(Dept)
-MIN(ROW(Dept)),,))))
I've tried to adapt your formula above to work with a similar column
on another worksheet; the main difference being the value in the
cells are formula which display the relevant text "103/1". I've
tried to use the INDIRECT and ADDRESS functions unsuccessfully.

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

Kind regards
QTE

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

 

[QTE]

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)),,))))

*

Hi
normally no need to change the formula. But could you post you
formula which produces the "103/1" value. Maybe there're some space
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 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

 

[QTE]

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

 

[Frank Kabel]

Hi
if you like email me your file

 

[QTE]

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.

*Hi
if you like email me your file

*[/QUOTE]
Kind regards
QT

 

[QTE]

Hi Frank,

*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
QTE *

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

Kind regards,
QT

 

[Frank Kabel]

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