Forum Discussion
Formula Help
Hi
I have a simple table, column A is a list of numbers, column B is a list of names, so pasted in cell A1 is as follows:
| 7 | Andrew Jones |
| 8 | David Smith |
| 9 | David Smith |
| 0 | David Smith |
| 4 | Andrew Jones |
| 3 | Andrew Jones |
| 2 | Andrew Jones |
| 15 | Charles Bridge |
| 20 | Charles Bridge |
| 9 | Charles Bridge |
| 6 | David Smith |
| 4 | Andrew Jones |
| 5 | Andrew Jones |
| 7 | Andrew Jones |
I'm looking for a formula that gives me the number of entries from the last 3 entries per name under a specific number, so for example number of entries for Andrew Jones under 11 would be 3, for Charles Bridge it would be 1. There will be more entries in both column A and B as time goes on. Thanks for any help
Rich_950 In cells D1 and E1 I've included the values "11" and "Andrew Jones" which are variable cells - I'd personally opt for a dropdown menu using UNIQUE but I'll answer your question first.
I've also assumed these are in date order? But I'd recommend you add a date column and modify the formula to filter based on the top 3 etc.
=SUM(IF(TAKE(FILTER(A1:A14,B1:B14=E1),3)<D1,1,0))One issue with your example is though that it's not clear if you want the top or bottom 3 - the above example assumes that you want the top 3 and that new entries will be at the top of the list.
Your example would result in 3 for Andrew Jones and 1 for Charles Bridge regardless of whether you want to take from the top or bottom becuase there are only 3 entries for Charles & all entries for Andrew are below 11.
I'd probably just sortby the row number and reverse the data in the formula if you needed it to take from the bottom.
- Hi Max, thanks for the quick reply, yes they will be in date order, bottom the most recent. so its a formula to take it from the bottom ('last' ) 3 entries. What would the formula be for that. Thanks
