Offset formula against visible cells only

[moogthemoog]

Hi

I have a list of names, and I am using a formula to calculate the total of individual names. Extract of spreadsheet is below (sorry, I'm not sure how to format it better in this post)

ColumnA ColumnB
Name1 1
Name2 1
Name2 0
Name2 0
Name3 1
Name3 0
Name4 1

The formula used in Cell B2 is =IF($A2=OFFSET($A2,-1,0),0,1)
I can then sum column B to calculate how many individual names are in the list (i.e. 7 rows of data, but only 4 separate names)

This works fine; however, if I filter the data, the offset won't always work - so, is there a way to use the OFFSET function on visible cells only?

I'd prefer not to use VBA, as many of the users have their security settings set to Macros turned off.

Thanks
Jon

P.S. Is there any way of showing the Excel spreadsheet in these posts? I realise I can use

 for VBA, but can't see a way to include the spreadsheet almost as a screenshot.

 

[Molden]

You could use the subtotal formula =SUBTOTAL(109,B2:B20)

 

[moogthemoog]

Hi

Thank you for your quick reply.

This will certainly subtotal the visible cells, but my problem is that if, say, row 6 is filtered out, the OFFSET formula in B7 will refer to one row up from A7: i.e. A6, rather than A5 (the next visible row up).

Thanks
Jon

 

[Molden]

Ahhh I get you.

Could you not do a count in column B instead then your total somewhere else like the below

this would put a 1 in the first entry and a 0 for the rest you could then sub total the list then?

ColumnA ColumnB
Name1 =IF(COUNTIF($A$1:A2,A2)>1,0,COUNTIF($A$1:A2,A2))
Name2
Name2
Name2
Name3
Name3
Name4

** presuming your list starts in row 2 then put my formula in b2 then copy down

 

[steve the fish]

If you want to count unique you could try:

=SUM(IFERROR(1/COUNTIF(A1:A100,A1:A100),0))

Its an array formula so needs to be entered CNTL-SHIFT-ENTER.

 

[moogthemoog]

Steve the fish: this unfortunately doesn't work when I filter my results. Below is including the Filter column in column C (filtering on "y").

Molden, similarly, this also doesn't appear to work.

Looks like I may have to introduce VBA to the spreadsheet after all!

ColumnA ColumnB ColumnC
Name1 1 y
Name2 1 y
Name2 0 y
Name2 0 y
Name3 1 n
Name3 0 y
Name4 1 n

 

[Aladin Akyurek]

@moogthemoog

Control=shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(Name,ROW(Name)-ROW(INDEX(Name,1,1)),,1)),MATCH(Name,Name,0)),ROW(Namer)-ROW(INDEX(Name,1,1))+1),1))

where Name refers to a range like A2:A100 (subject to filtering).

=ROW(Name)-ROW(INDEX(Name,1,1)+1