How to add values using another value until another value is reached
Hi everyone, Sorry for the confusing title but couldn't think how to describe it! I'm stuck on a formula at work and wondered if you can help?
excel.JPG
What I need to do is where there is a value in column D add all the values in column F until another value appears in column D again, then continue to do the same down the sheet.
Any help would be great.
Carl
So just to be clear (and i'm not sure this can be done) you want, where the value 7.33 is in cell D2, a formula that would add the values in F2, F3 and F4, then replace the 7.33 with those numbers? Is that correct?
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
No sorry, I knew my explanation sounded ok in my head but not on paper!
Where 7.33 is in D2, a formula say in the G column to add the values in F2, F3 and F4 together then when 7.33 is reached again in column D start again fresh so values F5 and F6 are totalled seperatly
excel2.JPG
Giving this result
so I don't have to try to recreate your data is it possible for you to post the sample you are using instead of posting jpgs? Go advanced to do that, make sure any confidential / proprietary data is removed.
No problem, I'm trying to automate the yellow column rather than me having to manually go through months worth of data!
Cheers
this would be one way though it is a bit sloppy, hopefully someone else has a better way and I'll keep thinking about it.
Hi Carl, try this array formula in G2 and copy down till G36.
ps: press ctrl+shift+enter instead of enter for array formula
Formula:
{=IF(D2<>"",SUMPRODUCT(F2:F$36*(ROW(D2:D$36)-ROW(D2)<IFERROR(MATCH(TRUE,D3:D$36<>"",0),ROW(D$36)-ROW(D2)+1))),"")}
_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved
Alvin
Thanks Sambo kid and Alvin-chung
Both are close, Sambo has the right result but Alvin you have the right layout with the spaces but the wrong end result as it adds the results together, I need seperate results.
Very much appreciate your help.
Hi Carl, I think you didn't use array formula as ps in my earlier post
Try click on cell G2, delete it, press F2, copy paste the formula below and hit ctrl+shift+enter (not just enter)
Formula:=IF(D2<>"",SUMPRODUCT(F2:F$36*(ROW(D2:D$36)-ROW(D2)<IFERROR(MATCH(TRUE,D3:D$36<>"",0),ROW(D$36)-ROW(D2)+1))),"")
_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved
Alvin
Look at column L on this one...
Spot on, thank you, I pressed Ctrl-Alt-Enter instead! so used to Ctrl-Alt-Dlt I think!
What does Controll-Shift-Enter do exactly?
http://office.microsoft.com/en-us/ex...001087290.aspx
A quick introduction to arrays and array formulas
If you've done even a little programming, you've probably run across the term array. For our purposes, an array is just a collection of items. In Excel, those items can reside in a single row (called a one-dimensional horizontal array), a column (a one-dimensional vertical array), or multiple rows and columns (a two-dimensional array). You can't create three-dimensional arrays or array formulas in Excel.
An array formula is a formula that can perform multiple calculations on one or more of the items in an array. Array formulas can return either multiple results or a single result. For example, you can place an array formula in a range of cells and calculate a column or row of subtotals. You can also place a formula in a single cell and calculate a single amount. An array formula that resides in multiple cells is called (logically enough) a multi-cell formula, and an array formula that resides in a single cell is called a single-cell formula.
The examples in the next section show you how to create multi-cell and single-cell array formulas.
<><><><>
BTW, w/my change you can get rid of the values in columns I, J and K and only use the added column C for the concatenation.
Thanks Sambo and Alvin, you've been a great help.
Glad I could help as I'm sure Alvin is too. And thanks for the bump to the reputation.
Don't forget to mark the post as solved using thread tools at the top of the post.
Hi Carl, you're welcome and thank you for your feedback
Thanks Sambo for the explanation of array formula.
_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved
Alvin
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Car7os 
Register To Reply
Bookmarks