Sum column if cells are outside range (range = lef

问题:

Please see the picture. In the matrix pictured, an entry is considered "New Business" from the leftmost value + the next 11 columns (so 12 months total). I've highlighted this "window" in yellow. Anything to the right of that window is "Return Business". For each column/month, I need to calculate both the New & Return Business. I need a formula or some method to derive these two sums from one column. I only need to be able to get one of these, because then I could just subtract it from the total column sum for the other value.

In cell O6 the result is only $25 for each because the leftmost value on row 5 does not extend a full 12 months/columns before column O, so anything within that 12 month window is still considered "New" (I made this window blue so you could see it extends beyond the cell I'm trying to get a result for). All 11 columns to the right of the first value in a row could be populated, but they would all still be new. Only from the 13th column on would anything be considered "Return".

As you can see, the first value in each row does not always occur in the same spot, it moves.

The spreadsheet I am working with is 2,000+ rows. I actually went through and did this manually, but that was NOT fun! I manually found the leftmost value in each row, counted out 11 cells after it, and then colored the rest of the row yellow. Then I used a custom function that allowed me to sum based on cell color. So if there is a way to conditionally format the values, that would work too, I could use my color sum function.

Additionally, if there is a better way to do this in R, please let me know. I am new to R, but I imagine matrix math is easier/faster to do, but I've only played around with it a bit and I've never had to do anything like this in it.

回答1:

I added a helper column C that finds the first non blank in the row (my data went from column D to column AZ)

=MATCH(TRUE,INDEX((D2:AZ2<>0),0),0)

This was then the formula I put in cell D5 and copied it across

=SUMIFS(D2:D4,$C2:$C4,">"&COLUMN(D1)-12-3)

The 12 is for 12 months and the 3 is for Column C where the data starts.

The return business is in D6 and across

=SUM(D2:D4)-D5

回答2:

This shouldn't be too difficult to do in VBA. Rather than just writing all of the code for you, I will set out how I would plan to approach this and suggest that you at least have a go at the code yourself, then open further questions for any specifics you are stuck on.

I would look to do something like the following:

1. Specify the range that you are working with;
2. Loop through each row getting the new business and return business values and adding them to cumulative totals;
3. On each row use a function to determine the first used row (e.g. .End(xlToRight) from column B on the current row may be helpful), then sum the range from this to a column offset of 12 to get the new business for that row and use this to derive the return business as you suggested;
4. Use your cumulative totals to update the fields required.

If you are stuck with any syntax manually performing the steps whilst recording a macro then looking at the code generated can often help.