Hello!
I'd like to make a weekly sum but with a dynamic criteria which I can choose.

I have a column with a formula that gives me negative and positive values. The weekly sum only starts when this column is above or equal 1. I want to sum 5 by 5 days (5 by cells from another page in the spreadsheet) by order of appearance.
Somewhat I could manage a formula to do this but it only works with increasing values (using SMALL formula) but it doesn't work if the third sum of 5 days is less than the second, or the fifth is less than the first... or, etc... because the column used to calculate varies with positive values and negative values, e.g, is has expenses and incomes.

Basically, I want to do a Weekly (choosing n-days) Sum but with the flexibility to choose which day is the starting cell (1st Monday, 1st Tuesday, or the 2nd Friday, 2nd Sunday.. ) and how many days I want to sum ( 2 by 2, 3 by 3, 5 by 5, 7 by 7)...

I hope the images attached (2 examples) can help you to help me to solve this problem...

Many Thanks!

P.S: I don't know how to use macros very well...

It would be more helpful if you posted sample workbooks with sample data so we could present sample answers back specific to your situation.


METHOD 1: Array formula

Enter this formula in C4 and confirm it with CTRL-SHIFT-ENTER to activate the array:
=IF(ROWS($A$1:$A1) > COUNT(B:B), "", INDEX($B$1:$B$30, SMALL(IF(ISNUMBER($B$1:$B$30), ROW($B$1:$B$30), ""), ROWS($A$1:$A1))))

If the correct first entry appears, then copy that cell down the rest of the data set. If you get a #VALUE error, you didn't press CTRL-SHIFT-ENTER (CSE) so the array is broken. Press F2, then CSE to get it going.

CAVEAT: array formulas are performing a LOT of calculations within a single cell. So if your dataset is large, this approach can/will affect sheet performance, sometime quite harshly.

============

METHOD 2: Add a helper column

This technique has the added benefit of not suffering from any performance issues. It does not use array formulas or volatile formulas, so even though it uses a lot of formulas, they don't affect speed.

In an empty column, let's say column F is free, add this:

F4: "KEY"
F5: =IF(ISNUMBER(B5),N(F4)+1,N(F4))

... copy cell F5 down the dataset and a set of numbers will appear. These incrementing numbers provide an "index" that a simple MATCH() formula can use.

Now, in C5:

=IF(ROWS($A$1:$A1) > COUNT(B:B), "", INDEX($B:$B, MATCH(ROWS($A$1:$A1), $F:$F, 0)))
... and copy down.

Hi JB!
Thanks for your help! It helped me a lot! I chose the second method... and even made some improvements. Many many thanks! It worked like a charm!
:)

Quote:

---

Originally Posted by JBeaucaire

It would be more helpful if you posted sample workbooks with sample data so we could present sample answers back specific to your situation.


METHOD 1: Array formula

Enter this formula in C4 and confirm it with CTRL-SHIFT-ENTER to activate the array:
=IF(ROWS($A$1:$A1) > COUNT(B:B), "", INDEX($B$1:$B$30, SMALL(IF(ISNUMBER($B$1:$B$30), ROW($B$1:$B$30), ""), ROWS($A$1:$A1))))

If the correct first entry appears, then copy that cell down the rest of the data set. If you get a #VALUE error, you didn't press CTRL-SHIFT-ENTER (CSE) so the array is broken. Press F2, then CSE to get it going.

CAVEAT: array formulas are performing a LOT of calculations within a single cell. So if your dataset is large, this approach can/will affect sheet performance, sometime quite harshly.

============

METHOD 2: Add a helper column

This technique has the added benefit of not suffering from any performance issues. It does not use array formulas or volatile formulas, so even though it uses a lot of formulas, they don't affect speed.

In an empty column, let's say column F is free, add this:

F4: "KEY"
F5: =IF(ISNUMBER(B5),N(F4)+1,N(F4))

...copy cell F5 down the dataset and a set of numbers will appear. These incrementing numbers provide an "index" that a simple MATCH() formula can use.

Now, in C5:

=IF(ROWS($A$1:$A1) > COUNT(B:B), "", INDEX($B:$B, MATCH(ROWS($A$1:$A1), $F:$F, 0)))
...and copy down.

---

Hi JB!
Thanks for your help! It helped me a lot! I chose the second method... and even made some improvements. Many many thanks! It worked like a charm!:)

My pleasure, glad it helped!