Construction Quote Log

I'm reworking my company's log of the proposals we send out, trying to indicate the total dollars that we could conceivably be awarded.

The challenge I'm having goes something like this: We are a vendor and bid our product to installers that are themselves bidding on construction projects. In a given month there may be ten projects where we offer pricing to say five installers. Ten projects with five installers each = 50 quotes - but still there's only 10 quotes (projects) that we could conceivably be awarded.

Additionally, commonly each project can go through something like two revisions before it's awarded. This means that we end up sending 100 quotes - but still there's only the ten projects we could conceivably be awarded.

How can I derive the total of just the potentially awardable projects?

Is there a way to indicate only the latest awardable dollar revision of a quoted project in an "Awardable $" column?

A simplified version of what I'm hoping to accomplish looks like this:

SO25210139 question example


To answer your first question:

In your attached example cell E12 is simply the summation of cells E2:E11 and will automatically update based on the changes you make to the cells within the E2:E11 range.

In Excel, go to the cell you want the totals to appear -> find your sum formula -> click it -> select the cell ranges you want to total up -> done.

To answer your second question:

I can't :) I could take a stab if you provide more info.