SUBTOTAL Second Count / sub-count of Filtered Visible Cells

SUBTOTAL Second Count / sub-count of Filtered Visible Cells

Post by QTE » Fri, 30 Jul 2004 07:09:33


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

Post by Frank Kabe » Fri, 30 Jul 2004 07:17:37

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

Post by QTE » Sat, 31 Jul 2004 01:33:03

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

Post by QTE » Mon, 02 Aug 2004 02:22:19

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

Post by Frank Kabe » Mon, 02 Aug 2004 02:53:10

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

Post by QTE » Mon, 02 Aug 2004 06:10:37

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

Post by QTE » Tue, 03 Aug 2004 10:51:16

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

Post by Frank Kabe » Tue, 03 Aug 2004 14:38:18

> 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

Post by QTE » Wed, 04 Aug 2004 02:07:53

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

Post by QTE » Wed, 04 Aug 2004 21:23:53

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

Post by Frank Kabe » Wed, 04 Aug 2004 21:46:30

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

Post by QTE » Wed, 04 Aug 2004 23:29:32

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

Post by QTE » Fri, 06 Aug 2004 00:37:55

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

Post by Frank Kabe » Fri, 06 Aug 2004 00:47:59

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