+ Reply to Thread
Results 1 to 12 of 12

Counting only Unique Entries

  1. #1
    Registered User
    Join Date
    02-17-2013
    Location
    Cape Coral, FL
    MS-Off Ver
    Excel 2010
    Posts
    5

    Counting only Unique Entries

    I need a formula to tally a list of birds that are seen by my birding club. It is a little more complicated than just addding totals of columns.
    For example:

    Here is the monkey wrench.
    In 2010 we saw 100 birds = 100 birds
    In 2011 we saw 100 birds but 20 of the birds were different from the birds we saw in 2010 so the total number of new birds for both years seen is 120 birds
    In 2012 we saw 90 birds but 10 of those were entirely different from the birds we saw in either 2010 or 2011, so the running total is now 130 birds.

    So I need a running total of the number of different kinds of birds that were seen over the years.
    The left side of the spread sheet I have all the birds listed and the top has the last 10 years for which we have been collecting stats (which needs to be added to in future years)

    The right side of the spread sheet shows a total of how many years the individual bird has been seen and the bottom row will be a total of how many birds were seen that particular year. The data has not been entered as yet, so changes can easily be made if necessary.
    Figuring out that Grand Grand total has stumped everyone I have asked.

    Anyone up for the challenge??? It would be a valuable statistic for our club.
    Thanks,
    The Owl Lady
    Last edited by TheOwlLady; 02-17-2013 at 12:09 PM.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Need an unusual formula created

    Can you upload an example of your spreadsheet (Go Advanced> Manage Attachments)?
    Also, Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

    Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

    To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
    I suggest "Counting only Unique Entries" or something like that.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    02-17-2013
    Location
    Cape Coral, FL
    MS-Off Ver
    Excel 2010
    Posts
    5

    Counting only Unique Entries

    Thanks for the title help. I though it was an OK title. Attached is the spreadsheet I set up. The check marks are only samples as I have not entered the data as yet.
    Attached Files Attached Files

  4. #4
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Counting only Unique Entries

    It would be better to enter values so we can test solutions.
    Enter numbers you entering manualy and also mark expected result (in red for example).

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Counting only Unique Entries

    This formula will count if cells has data (unblank)
    In C427:
    Please Login or Register  to view this content.
    Drag accross
    Hope this helps.
    Attached Files Attached Files
    Quang PT

  6. #6
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Counting only Unique Entries

    See if this workbook gives you a start.

    It counts the species seen for each year, the new species seen in the year, and gives a yearly running total.

    We can improve on this to make it more dynamic, if you feel it suits your needs.

    To test fill in some "x"s in the grid.

    [EDIT]
    All the best on your twitching, don't pisshh pissh about to much ...
    The Fife Bird Club
    Attached Files Attached Files
    Last edited by Marcol; 02-17-2013 at 03:02 PM.
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  7. #7
    Registered User
    Join Date
    02-17-2013
    Location
    Cape Coral, FL
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Counting only Unique Entries

    Thanks to both of you for creating the formula's. Both work perfectly but I have to go with the "copy of Florida Birds" version. I like how the top explains the numbers. I am going to close out the thread as solved. Several of the members of our club are over 90 years old and have been members for over 50 years. They will love this form.
    Thanks again for all your help and quick at that.

    The Owl Lady
    Last edited by TheOwlLady; 02-17-2013 at 04:13 PM.

  8. #8
    Registered User
    Join Date
    02-17-2013
    Location
    Cape Coral, FL
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Counting only Unique Entries

    Whoops, forgot 1 question. Next year I will have to add 2014 to the years and so on. Will that change the formula?

  9. #9
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Counting only Unique Entries

    Next year I will have to add 2014 to the years and so on. Will that change the formula?
    We need to make a few adjustments, to make this totally dynamic, look back tomorrow.

    We have Surf Scoters in Fife at present, quite rare this side of the pond, might look for them first!

    Several of the members of our club are over 90 years old
    My legs,and eyes are giving up at 60+ ... Respect to them all ...

  10. #10
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Counting only Unique Entries

    Please...just a hint...do not post ads here!!....I see enough in my e-mail, I do not want to see them where I relax
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  11. #11
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Counting only Unique Entries

    Try this workbook, it uses a couple of dynamic named ranges.

    In C1 I have inserted a formula to return the current year, this is then referred to by the other formulae, best not to move this cell.

    The formulae in C2:C4 right will now only count the current year and earlier, as will the formulae in A6 down.

    Column B6 down is now the dynamic name "SpeciesList" this will grow as you add to your list, and the other related formula will adjust automatically.

    To extend the table just drag the last row down. (Row 455 at present)
    Similarly, drag the last column right, the columns will not be "activated" until the year arrives.

    Try changing the years in C5:M5 to some future year to see what hapens in the rows above and below.

    Look at the conditional formatting for B6 down, if you type an entry in this column all in uppercase the cell will be highlighted. This should help to separate family groups.

    Hope this helps.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    02-17-2013
    Location
    Cape Coral, FL
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Counting only Unique Entries

    Marcol,
    I have finally completed entering the 13 years of back data into the wonderful formula you created for my birding club. It has been well received by the membership. I do have a few tweaks that I would like make if possible.
    In row 5, can the years be changed to read 2001-02, 2002-03 etc. Our year runs from November to April, so the two year format would make the data clearer. I tried but it messed up the formula.


    Can we have the ability to add a bird in the middle of the spreadsheet rather than having to all them to the end? Some birds do need to go at the end but once in a while we find one that needs to be put under one of the main categories. Also sometimes birds split into two categories, so there would be a new bird to add to the list.

    In cells 3-A and 4-A, can that "true-false" be hidden or eliminated?

    In cell a-455, can the Christmas Bird Count Included be put on two lines in one box without making the box wider?


    In column 3 there is no total for the number of new species seen in year this is because there were none. Could it show "0" rather than blank?

    Thanks in advance.
    Beverly

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1