Updating previous entries in Excel automatically

[fisherman1313]

Hello evryone, I'm a new member in need of some help.

I'm a birder and keep many lists of the bird species I have seen. One of my lists is a spreadsheet (in Excel 2010) containing an entry for each of the birding trips I've been on. On this list I enter things such as date, location, county, number of species seen that day and so on. I also enter the total number of species seen for; 1) the year, 2) the county I was birding in and 3) my lifetime. Next to each of the three total columns is the percentage of the total seen on that particular day. Because the list is ordered chronologically and I bird in more than one county I have several entries for my home county seperated by entries for other counties. When I see a new species in my home county I have to either make multiple entries to update the total or I have to sort the county column to group all the rows for home county, update the column, then resort by date to put the list back in order. I need to find out if there is a way to link the county column with the county total column so that the total column will automatically update whenever I change the total on any line for that county.

Thanx in advance for any assistance.

[mrice]

Sounds like you might need to look at using the COUNTIF function. See Excel help for details or post a copy of your workbook if you need further help.

[fisherman1313]

Thanx for the input Martin, but isn't the countif function for counting cells that meet a certain criteria? I don't need to count the cells, I need all the cells that corespond with specific other cells to update automatically when I update any of the other cells that corespond with those cells. Here's an example:
Date Total County County Total % Life Total % Year Total %
07/08/12 58 STA 219 26.48% 290 20.00% 244 23.77%
07/14/12 47 MER 149 31.54% 290 20.00% 244 19.26%
07/15/12 64 STA 219 29.22% 290 22.07% 244 26.23%
07/16/12 16 SJ 180 8.89% 290 5.52% 244 6.56%
07/22/12 64 STA 219 29.22% 290 22.07% 244 26.23%
07/22/12 20 STA 219 9.13% 290 6.90% 244 8.20%
08/19/12 38 STA 219 17.35% 290 13.10% 244 15.57%
08/26/12 68 SM 93 73.12% 290 23.45% 244 27.87%
09/04/12 33 STA 219 15.07% 290 11.38% 244 13.52%
09/04/12 17 STA 219 7.76% 290 5.86% 244 6.97%
09/05/12 24 CAL 112 21.43% 290 8.28% 244 9.84%
09/05/12 24 CAL 112 21.43% 290 8.28% 244 9.84%
09/08/12 35 STA 219 15.98% 290 12.07% 244 14.34%
09/09/12 65 STA 219 29.68% 290 22.41% 244 26.64%
09/16/12 56 STA 219 25.57% 290 19.31% 244 22.95%
09/17/12 21 STA 219 9.59% 290 7.24% 244 8.61%
09/18/12 23 STA 219 10.50% 290 7.93% 244 9.43%
09/23/12 67 STA 219 30.59% 290 23.10% 244 27.46%
09/25/12 34 STA 219 15.53% 290 11.72% 244 13.93%
If I see a species I've never seen before in my life I update the entire Life Total column. When I see a species I have not seen yet this year I update the entire Year Total column. If I go to a different county and see a species I have never seen before in that county I only update the entries for that county. Since the list is ordered chronolagically I either have to update several rows individually or sort by county, update, then re-sort by date. I'm looking for a way to match the entries under county for say STA with the corresponding entries for County Total in that row so that if I update one entry in the County Total column for STA to 220 all the other rows for STA would automatically update to 220.

[mrice]

If I have understood correctly, it might be easier to simply add a new row to the bottom detailing the date, county and species and let formulae do the calculations without worrying which column to update..

I'm thinking that the SUMIFS function may be of use to you coupled with the YEAR function to extract the year from the date. Does this make sense?