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.

  • I'm not getting exactly what you mean... Is it... If you have B1-B4 all as 3000, then you would want that 3000 split into 4 parts, so 750 (3000/4) is shown?... Then the next rnage So perhaps, you can have B1-->B4 as total amount for that company... then C1-->C4 the amount for each of the counties? – Brian Folan Oct 31 '14 at 21:12
  • Sounds like you want to use a pivot table. – LPChip Oct 31 '14 at 21:30
  • Suggest you provide a second set of counties with a different funding amount as well as the results that you hope to achieve. – Jeeped Nov 1 '14 at 13:10

There are probably better ways to format your data. For instance, what if two requests in a row are for the same amount? You need a unique ID for each request so you can distinguish between them. However, to answer you actual question, I believe your goal is to count how many instances of the value in B exist in a row and then divide the value by the number of instances. To that end, I suggest the following:

  1. Add a header row. You don't have to, but it makes the formula much simpler because you don't have to error check for when you're on the first row.
  2. Use this formula in cell C2 and copy / paste down as needed:
    If the value is the same as the previous, then just copy its calculations. Otherwise, count how many of the same value are in a row and then divide the value by that count.
    Note that I assumed there would be less than 1,000 in a row because that should cover it.

This would be a lot easier if you would add a column to identify funding requests.  It can be out of sight; e.g., column Z.  Set Z1 to 1, and set Z2 to

=IF(B1=B2, Z1, Z1+1)

and drag that down to the bottom of your data.  Then the denominator you want is

COUNTIF(Z$1:Z$9999, Z1)

(adjusting the 9999 to cover all your data), and so C1 can be

=B1 / COUNTIF(Z$1:Z$9999, Z1)

Set C2 TO 1 and write this formula in C2: =IF(B3=B2,(C2+1),1)

Screenshot of formula

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.