I had placed this type of question, as counting unique entries, around some
time back and some pals did try 2 help out with COUNTIF, but I guess I was
not clear enough or couldn't understand.

Lets have another example:

Subtotals help a lot to sum or count ONLY the "VISIBLE" cells appearing in
the "FILTERED" data.

Now the question is that when I filter the data, I want to count the
reoccuring entries appearing in one of the columns to be counted as once only
returning the number of unique entries only.

For example:

CASE BRANCH PRODUCT AMOUNT
==== ====== ====== ======
CASE1 BRANCH1 PROD1 293,297
CASE2 BRANCH1 PROD2 901,310
CASE3 BRANCH1 PROD3 572,292
CASE1 BRANCH1 PROD4 686,653
CASE2 BRANCH1 PROD1 758,984
CASE3 BRANCH2 PROD2 901,086
CASE1 BRANCH2 PROD3 494,941
CASE2 BRANCH2 PROD4 79,819
CASE3 BRANCH2 PROD1 649,114
CASE1 BRANCH2 PROD2 285,442
CASE2 BRANCH3 PROD3 340,301
CASE3 BRANCH3 PROD4 536,027
CASE1 BRANCH3 PROD1 652,562
CASE2 BRANCH3 PROD2 453,632
CASE3 BRANCH3 PROD3 775,057
CASE4 BRANCH3 PROD4 889,722

Upon filteration on the basis of BRANCH1 number of cases should be 3 only
(as CASE1 & CASE2 each occurring twice being counted once and CASE3 being
counted too therefore result being only 3).

Upon filteration on the basis of PROD4 number of cases should be 4 (as all
the cases are different and are 4 in number).

It would be preferable if you copy the data and paste on an XL sheet and try
out after applying Autofilter.

All the help and expertise shall be highly obliged.

Thanx in advance.

Assumptions:

A1:D1 = column headers
A2:D17 = data
No empty/blanks cells within the data area

Try this array formula** :

=SUM(--(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A1,ROW(A2:A17)-1,,)),MATCH(A2:A17,A2:A17,0)),IF(SUBTOTAL(3,OFFSET (A1,ROW(A2:A17)-1,,)),MATCH(A2:A17,A2:A17,0)))0))



--
Biff
Microsoft Excel MVP


"FARAZ QURESHI" wrote in message
...
I had placed this type of question, as counting unique entries, around some
time back and some pals did try 2 help out with COUNTIF, but I guess I was
not clear enough or couldn't understand.

Lets have another example:

Subtotals help a lot to sum or count ONLY the "VISIBLE" cells appearing in
the "FILTERED" data.

Now the question is that when I filter the data, I want to count the
reoccuring entries appearing in one of the columns to be counted as once
only
returning the number of unique entries only.

For example:

CASE BRANCH PRODUCT AMOUNT
==== ====== ====== ======
CASE1 BRANCH1 PROD1 293,297
CASE2 BRANCH1 PROD2 901,310
CASE3 BRANCH1 PROD3 572,292
CASE1 BRANCH1 PROD4 686,653
CASE2 BRANCH1 PROD1 758,984
CASE3 BRANCH2 PROD2 901,086
CASE1 BRANCH2 PROD3 494,941
CASE2 BRANCH2 PROD4 79,819
CASE3 BRANCH2 PROD1 649,114
CASE1 BRANCH2 PROD2 285,442
CASE2 BRANCH3 PROD3 340,301
CASE3 BRANCH3 PROD4 536,027
CASE1 BRANCH3 PROD1 652,562
CASE2 BRANCH3 PROD2 453,632
CASE3 BRANCH3 PROD3 775,057
CASE4 BRANCH3 PROD4 889,722

Upon filteration on the basis of BRANCH1 number of cases should be 3 only
(as CASE1 & CASE2 each occurring twice being counted once and CASE3 being
counted too therefore result being only 3).

Upon filteration on the basis of PROD4 number of cases should be 4 (as all
the cases are different and are 4 in number).

It would be preferable if you copy the data and paste on an XL sheet and
try
out after applying Autofilter.

All the help and expertise shall be highly obliged.

Thanx in advance.

YAHOO!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
THANX!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

"T. Valko" wrote:

Assumptions:

A1:D1 = column headers
A2:D17 = data
No empty/blanks cells within the data area

Try this array formula** :

=SUM(--(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A1,ROW(A2:A17)-1,,)),MATCH(A2:A17,A2:A17,0)),IF(SUBTOTAL(3,OFFSET (A1,ROW(A2:A17)-1,,)),MATCH(A2:A17,A2:A17,0)))0))



--
Biff
Microsoft Excel MVP


"FARAZ QURESHI" wrote in message
...
I had placed this type of question, as counting unique entries, around some
time back and some pals did try 2 help out with COUNTIF, but I guess I was
not clear enough or couldn't understand.

Lets have another example:

Subtotals help a lot to sum or count ONLY the "VISIBLE" cells appearing in
the "FILTERED" data.

Now the question is that when I filter the data, I want to count the
reoccuring entries appearing in one of the columns to be counted as once
only
returning the number of unique entries only.

For example:

CASE BRANCH PRODUCT AMOUNT
==== ====== ====== ======
CASE1 BRANCH1 PROD1 293,297
CASE2 BRANCH1 PROD2 901,310
CASE3 BRANCH1 PROD3 572,292
CASE1 BRANCH1 PROD4 686,653
CASE2 BRANCH1 PROD1 758,984
CASE3 BRANCH2 PROD2 901,086
CASE1 BRANCH2 PROD3 494,941
CASE2 BRANCH2 PROD4 79,819
CASE3 BRANCH2 PROD1 649,114
CASE1 BRANCH2 PROD2 285,442
CASE2 BRANCH3 PROD3 340,301
CASE3 BRANCH3 PROD4 536,027
CASE1 BRANCH3 PROD1 652,562
CASE2 BRANCH3 PROD2 453,632
CASE3 BRANCH3 PROD3 775,057
CASE4 BRANCH3 PROD4 889,722

Upon filteration on the basis of BRANCH1 number of cases should be 3 only
(as CASE1 & CASE2 each occurring twice being counted once and CASE3 being
counted too therefore result being only 3).

Upon filteration on the basis of PROD4 number of cases should be 4 (as all
the cases are different and are 4 in number).

It would be preferable if you copy the data and paste on an XL sheet and
try
out after applying Autofilter.

All the help and expertise shall be highly obliged.

Thanx in advance.

You're welcome!

--
Biff
Microsoft Excel MVP


"FARAZ QURESHI" wrote in message
...
YAHOO!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
THANX!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

"T. Valko" wrote:

Assumptions:

A1:D1 = column headers
A2:D17 = data
No empty/blanks cells within the data area

Try this array formula** :

=SUM(--(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A1,ROW(A2:A17)-1,,)),MATCH(A2:A17,A2:A17,0)),IF(SUBTOTAL(3,OFFSET (A1,ROW(A2:A17)-1,,)),MATCH(A2:A17,A2:A17,0)))0))



--
Biff
Microsoft Excel MVP


"FARAZ QURESHI" wrote in message
...
I had placed this type of question, as counting unique entries, around
some
time back and some pals did try 2 help out with COUNTIF, but I guess I
was
not clear enough or couldn't understand.

Lets have another example:

Subtotals help a lot to sum or count ONLY the "VISIBLE" cells appearing
in
the "FILTERED" data.

Now the question is that when I filter the data, I want to count the
reoccuring entries appearing in one of the columns to be counted as
once
only
returning the number of unique entries only.

For example:

CASE BRANCH PRODUCT AMOUNT
==== ====== ====== ======
CASE1 BRANCH1 PROD1 293,297
CASE2 BRANCH1 PROD2 901,310
CASE3 BRANCH1 PROD3 572,292
CASE1 BRANCH1 PROD4 686,653
CASE2 BRANCH1 PROD1 758,984
CASE3 BRANCH2 PROD2 901,086
CASE1 BRANCH2 PROD3 494,941
CASE2 BRANCH2 PROD4 79,819
CASE3 BRANCH2 PROD1 649,114
CASE1 BRANCH2 PROD2 285,442
CASE2 BRANCH3 PROD3 340,301
CASE3 BRANCH3 PROD4 536,027
CASE1 BRANCH3 PROD1 652,562
CASE2 BRANCH3 PROD2 453,632
CASE3 BRANCH3 PROD3 775,057
CASE4 BRANCH3 PROD4 889,722

Upon filteration on the basis of BRANCH1 number of cases should be 3
only
(as CASE1 & CASE2 each occurring twice being counted once and CASE3
being
counted too therefore result being only 3).

Upon filteration on the basis of PROD4 number of cases should be 4 (as
all
the cases are different and are 4 in number).

It would be preferable if you copy the data and paste on an XL sheet
and
try
out after applying Autofilter.

All the help and expertise shall be highly obliged.

Thanx in advance.