Hello all,
I'm trying to create a formula that will add all the values from a range of cells in one column (Column B) that are associated with a set of cells in another column (Column A), and in this other column there are many unique sets of similar values.
For example Column A (lake name) looks like this:
A1 = Red Lake
A2 = Red Lake
A3 = Blue Lake
A4 = Blue Lake
A5 = Blue Lake
A6 = Green Lake
A7 = Green Lake
A8 = Green Lake
A9 = Green Lake
...
A451 = Turquoise Lake
A452 =Turquoise Lake
Column B (lake chlorine concentration) looks like this:
B1 = 5
B2 = 7
B3 = 1
B4 = 2
B5 = 3.5
B6 = 4
B7 = 3
B8 = 1
B9 = 2.25
...
B451 = 4
B452 = 3
In column C, I'd like to enter a formula that will yield the sum of the values from each range of cells in Column B that are associated with each unique set of similar values from Column A. So, the results in Column C would look like this:
C1 = 12 (sum of B1+B2, which are collectively associated with the UNIQUE SET of values in Column A, namely "Red Lake")
C2 = 12 (dito)
C3 = 6.5 (sum of B3+B4+B5, which are collectively associated with the UNIQUE SET of values in Column A, namely "Blue Lake")
C4 = 6.5 (dito)
C5 = 6.5 (dito)
C6 = 10.25 (etc....)
C7 = 10.25
C8 = 10.25
C9 = 10.25
...
C451 = 7
C452 = 7
Since there are about 200 lakes, I'm trying to avoid entering 200 different SUM formulas. I am an Excel novice, but I presume this would require an array formula, if even possible with a formula.
Thanks for any help offered!
Bookmarks