Excel formula to count when value changes?

[bbarge]

Hi all,

I have an Excel conundrum I can't seem to crack.

In Column A I have a long list of counties in a spreadsheet, 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--i.e. if an organization received $3,000 and claims to serve counties Smith, Robeson, Macon, and Duplin, the spreadsheet looks like this:

A1 Smith B1 3000
A2 Robeson B2 3000
A3 Macon B3 3000
A4 Duplin B4 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 for each particular request group (e.g., 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 (i.e. two 10,000 value requests) are adjacent to each other).

I've been using COUNT formulas, such as B1/(COUNT(B1:B4)) to get the values I need, but this strategy requires me to change the range manually. 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!

 

[jim may]

Obviously you can have duplicated Amounts ($3,000; $3,000).. hummm

 

[bbarge]

Right, but that's ok. As long as those amounts are the same, I want the formula to apply to those fields, because they represent the same proposal. It's when that number changes--signifying a new request--that I'd want the formula to automatically adjust as well. You're right that, within the entire worksheet, there may be multiple sets of counties from 3000-sized requests. That's the trick.

 

[Marcelo Branco]

See if this is ok

	A​	B​	C​
1​	Counties​	Funding​	Amount/County​
2​	County1​	3000​	750​
3​	County2​	3000​	750​
4​	County3​	3000​	750​
5​	County4​	3000​	750​
6​	County5​	6000​	1200​
7​	County6​	6000​	1200​
8​	County7​	6000​	1200​
9​	County8​	6000​	1200​
10​	County9​	6000​	1200​
11​	County10​	1500​	1500​
12​	County11​	3000​	3000​
13​	County12​	1200​	600​
14​	County13​	1200​	600​
15​	County14​	10000​	1000​
16​	County15​	10000​	1000​
17​	County16​	10000​	1000​
18​	County17​	10000​	1000​
19​	County18​	10000​	1000​
20​	County19​	10000​	1000​
21​	County20​	10000​	1000​
22​	County21​	10000​	1000​
23​	County22​	10000​	1000​
24​	County23​	10000​	1000​
25​	County24​	900​	450​
26​	County25​	900​	450​

Formula in C2 copied down
=IF(B2=B1,C1,B2/COUNT(B2:INDEX(B2:$B$10000,MATCH(TRUE,INDEX(B3:$B$10000<>B2,0),0))))

Hope this helps

M.

 

[jim may]

Someone else may come up with a formula, but I opted to write a macro... So with your spreadsheet looking like so...

Run this Macro (see below -- paste into a Standard module)

Excel 2012
	A	B	C
1	County	Joint Grant	Share Amts
2	Smith	3,000.00
3	Robeson	3,000.00
4	Macon	3,000.00
5	Duplin	3,000.00
6	ABC	6,600.00
7	DDD	6,600.00
8	FFF	6,600.00
9	Montgomery	3,000.00
10	Smith	3,000.00
11	Rankin	3,000.00
Sheet1

Sub Foo()
LR = Range("A" & Rows.Count).End(xlUp).Row
Cnt = 0
Set Rng = Range("A2:A" & LR)
    For Each C In Rng
        If C.Offset(, 1) = C.Offset(1, 1) Then
        Cnt = Cnt + 1
        Else
        Cnt = Cnt + 1
        C.Offset(-Cnt + 1, 2).Resize(Cnt, 1).Formula = "=(B" & C.Row - Cnt + 1 & "/" & Cnt & ")"
        Cnt = 0
        End If
    Next C
End Sub

 

[bbarge]

Many, many thanks to you both! I have tried the formula and it works perfectly. The macro is a great option too.

You have no idea how much time you have saved me and my coworkers.