counting unique entries

[mothman]

I've a variation on a theme (examined in other threads) that I'd appreciate some help with please... I wish to count the number of first time entries in a multi-column table of data. Let me explain:

in Column B I have the unique identifier (viz a list of the names of all the UK moth species)

in subsequent columns I record the number of each species seen on a given night (one night per column)

now I am happy using 'count' and 'sum' functions respectively to record the total number of species and the total number of individuals recorded each night

but I also want to keep a running total of the number of species I have seen (in total) during the year.

Could somebody please offer a suggestion as to how I can set this up so that, as I add new columns of records, I can keep a count of my year total of separate species recorded?

I'm on 82 so far (which I can count by hand - I've only just started!) but a fellow 'moth-er' is on 1008 which would be harder to keep track of!!

Many thanks


Mothman

 

[barry houdini]

Hello mothman, welcome to MrExcel

Assuming you have species in B2:B100 and columns across to Z are in use (so numbers are in C2:Z100). Try this formula for the number of different moths spotted

=SUMPRODUCT((COUNTIF(OFFSET(C2:C100,ROW(C2:Z100)-ROW(C2),0),">0")>0)+0)

adjust ranges as required

 

[mothman]

Many thanks - this makes sense - I will give it a go

Mothman

 

[mothman]

Hmmmm!

That doesn't seem to work I'm afraid.

Have tried it on a simple s/sheet and it appears to tell me the row position of the last number in column C minus1 - ie irrespective of other data entries in Column C or D-Z it only responds to the position of the last entry in Column C - ie if the last entry in Col C is a "5" in row 17, it returns the answer "16".

Any idea what I'm doing wrong?

Cheers

Mothman

 

[barry houdini]

Sorry, you're right - I specified one of the ranges wrongly - the first argument of OFFSET should be the first row of data, not the first column, i.e. it should be like this

=SUMPRODUCT((COUNTIF(OFFSET(C2:Z2,ROW(C2:Z100)-ROW(C2),0),">0")>0)+0)

 

[mothman]

That's the ticket! Will let you know when I get to 1000!

Many thanks

Mothman