how to make an auto-updating sample count column

Question

I am trying to create a worksheet to keep track of sample count. I have columns for Site Name, Quarter, and several other informative stats. I want to create a column that will update each time a new sample is added(i.e. 1, 2, 3... ) I have 12 sites, each of which I want the sample counter to be exclusively linked to.  Furthermore I want the counter to be futher categorized by the financial quarter (1-4).  The way I hope this to work is that say that one day I get samples at 2 sites.  I input a new row.  In the Site Name column I input "Site A", and that the sample was collected in Quarter "2".  I create another new row for the other site that I sampled "Site B" also collected in quarter "2".  In this example Site A has already had 5 samples this quarter(2), and site B has had 2 samples in this quarter(2).  I want my counter to automatically populate a "6" for sample count for Site A, and "3" for Site B.  I then want to use this counter to populate a bar graph indicating the progression toward the 15 samples per Quarter goal for each site.  Help!

pmroz · Answer

Create a formula column called Counter.  Add this formula to it:

current_row = row();

this_site    = :Site[current_row];

this_quarter = column("Quarter (1-4)")[current_row];

this_counter = 1;

for (i = 1, i < current_row, i++,

one_site    = :Site[i];

one_quarter = column("Quarter (1-4)")[i];

if (this_site == one_site & this_quarter == one_quarter,

this_counter++;

);

:Counter[current_row] = this_counter;

RVhydrA · Answer

here is a sample of what i'm working on, I changed some of the quarter designations for the purpose of this example

pmroz · Answer

This can be done using a formula column.  Could you post some data so we could see it more clearly?