I have an Excel conundrum I can't seem to crack.
In Column A I have a long list of counties, and each of those counties is a member of a larger group of counties that pertain to a particular funding request to our organization. In column B is the total funding amount in that request--e.g., if an organization received $3,000 and claims to serve counties Smith, Robeson, Macon, and Duplin, the spreadsheet looks like this:
Col A Col B (data starts in row 1) Smith 3000 Robeson 3000 Macon 3000 Duplin 3000
.. and the list goes on with counties from other requests, for about 1200 total row entries.
I'm trying to set up a function that allows the total amount (3000) to be divided up by the number of cells up until the B value changes. So, if the next B value is 6500, for example, the formula would only count A1-A4 in the denominator. This would theoretically allow me to drag the formula across the entire column, as opposed to manually changing the range every time there's a new grouping of counties. (The entries have already been ordered so that no two groupings of counties from a same-value request (e.g., two $10,000 requests) are adjacent to each other).
I have not been able to construct a COUNT formula that can account for an unknown range based on a value change rather than occurrence. I would appreciate any help you could provide! Oh, and happy Halloween.