Lookup depending on variable

**KieronP** · 01-06-2013, 01:56 PM

Hi,
I hope you can help.

I have a spreadsheet that I use for keeping track of birds that I and three other people ring. Each row contains species name, Location and who ringed it amongst other things. I have the formula below that I use to count the unique number of species that we have ringed, from the total number of animals that we have ringed. (Column E is called "species").

=SUM(IF(FREQUENCY(IF(LEN('2012'!E2:E399)>0,MATCH('2012'!E2:E399,'2012'!E2:E399,0),""), IF(LEN('2012'!E2:E399)>0,MATCH('2012'!E2:E399,'2012'!E2:E399,0),""))>0,1))

This works fine for an overall total but it would also be good if I could either filter or calculate the results by person's initals which is contained in another field. Is it possible to do this? Essentially I guess I am saying can I run the above formula only when the cell that contains the name = "KP"?

thanks

**dogberry** · 01-06-2013, 02:09 PM

Hi

Would it not be simpler and less prone to error or inadvertant change if you converted your range to a table then you can search on whatever you choose with no complicated formulas.

Chris

**icestationzbra** · 01-06-2013, 02:59 PM

sans a sample workbook, this ARRAY formula is the best i can come up with, assuming that the "initials" column spans F2:F399:

=SUM(IF(FREQUENCY(IF(LEN('2012'!E2:E399)*('2012'!F2:F399="KP"),MATCH('2012'!E2:E399,'2012'!E2:E399,0)), ROW('2012'!E2:E399)-MIN(ROW('2012'!E2:E399))+1),1))

**Tony Valko** · 01-06-2013, 05:54 PM

Like this...

A2:A399 = someones initials

=SUM(IF(FREQUENCY(IF('2012'!E2:E399<>"",IF('2012'!A2:A399="XX",MATCH('2012'!E2:E399,'2012'!E2:E399,0))),ROW('2012'!E2:E399)-ROW('2012'!E2)+1),1))

Replace the "XX" with the initials or better yet, put the initials in a cell and refer to that cell.

Don't forget... array enter!