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

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

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

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

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

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

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

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

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

Hi Frank,

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

much appreciated.

Kind regards

QT

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

much appreciated.

Kind regards

QT

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

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

> 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

---------

=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

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

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

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/

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/

Hi

if you like email me your file :-)

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

-MIN(ROW(Dept)),,))))

-----------

-----------

if you like email me your file :-)

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

-MIN(ROW(Dept)),,))))

-----------

-----------

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

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

Hi Frank,

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

Kind regards,

QT

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

Kind regards,

QT

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

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

1. Subtotals - How to count only "visible" / distinct values?

2. How can I count cells that meet two criteria within a filtered co.

3. VBA: For Count, when count changes from cell to cell

4. Count contiguous cells, or count cell clusters

5. count cells, then reset count when value in another cell changes

6. count cells, then reset count when value in another cell chang

7. Looking for a count and then a sub count

8. count elements in root, sub root sub-sub root treeview

9. How can I do a count subtotal AND a sum subtotal on the same row?

10. When I subtotal in Excel how can I count the number of subtotals?

11. Subtotal counting formula in cells

12. Count New to the List, Count Leaving the List, Count Total

13. how to get count(col1), count(col2), count(sol3) with only one query

14. count duplicats, display incremental count, restart count at change in value

15. populating array3 from two other arrays when count(array1) < count(array2)

14 post • Page:**1** of **1**