Excel -Count occurance of a value in a cell for filtered range

[neemi]

I have an excel spreadsheet which I am trying to count the occurance for only within a filtered range. Does anyone know how I can do this?

What I am ultimately trying to do is build this into a sub total row so that it only adds up the values once within the sumproduct. I thought if I can create a coulm which shows a culaitive count of the number of times the row appears then I could build this into the sumproduct and only includ where occuranceCount = 1.

Hope this makes sense?
help appreciated.
neemi

 

[SkipVought]

Hi,

Check out the SUBTOTAL function in Excel HELP.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[neemi]

any pointers as to how I would use subtotal to only count unique values? I am looking at frequency but not sure if this is correct. Also the values I want a unique count of is not a number but is text.

many thanks.
neemi

 

[SkipVought]

[tt]
=SUM(IF(FREQUENCY(MATCH(PrtNbr,PrtNbr,0),MATCH(PrtNbr,PrtNbr,0))>0,1))
[/tt]
entered as an ARRAY FORMULA counts unique values in the PrtNbr range (a named range on my sheet).

However, it does not respond to filtering.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

I'd be apt to use MS Query to return a DISTINCT list to another sheet, based on the filter selection criteria on the viewed sheet.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lionelhill]

or maybe you're getting into the sort of situation where creating a pivot table might help, and filter what you want the pivot table to display??

 

[GlennUK]

I thought if I can create a coulm which shows a culaitive count of the number of times the row appears then I could build this into the sumproduct and only includ where occuranceCount = 1.

I think I know what you mean ( I may be wrong, of course ). But, this formula will give a cumulative count of values for items, per row, but only increment for visible rows, so you'll get a result of 1 in the right places to use in your SUMPRODUCT. It's rather complicated ... I tried to simplify it, but got stuck. It's taken from Laurent Longre's method of calculating for visible rows in a filtered list:

=IF(SUMPRODUCT(SUBTOTAL(3,OFFSET($A$1:A2,ROW($A$1:A2)-MIN(ROW($A$1:A2)),,1)),--(ROW()=ROW($A$1:A2)))=1,SUMPRODUCT(SUBTOTAL(3,OFFSET($A$1:A2,ROW($A$1:A2)-MIN(ROW($A$1:A2)),,1)),--($A$1:A2=A2)),0)

.. placed in a cell in row 2, assuming that your data starts in row 2. And copied down.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[neemi]

thank you all for responding.

I ended up finding a solution and using a combination of SUMproduct and subtotal:

=SUMPRODUCT(SUBTOTAL(3,OFFSET($AL11:AL$11,ROW($AL11:AL$11)-MIN(ROW($AL11:AL$11)),,1)), --($AL11:AL$11=$AL11))

and then when formula's were copied down I could do a search for where the value of the column = 1 to include in a summation where totals where only included once.

again many thanks.