I have created a sheet which is tracking 3 items per client and a number of clients so I have set up a SUM function the only way I know how which is a long a1+a4+a7, etc
Its works fine, but if I want to add a new client and 3 new rows, ALL of the + after are now off.

Is there a better way to create a formula that will track say every 3rd cell in a column but then let you lengthen or shorten the column?

Thanks!!

In searching her I have figured out it is the DSUM function that I ma needing. I have tried a number of times - seems simple enough but I cant get it to work

=DSUM( D6:F405,"F",CF6:CF7)
D6:F405 is the area where the products and the totals are

F is the colums i need every 3rd cell to add

CF6 is the title of the critera and CF7 is the name to match

Any help wold be appreciated
Thanks

On Sun, 4 Mar 2012 01:02:03 +0000, ExcelNoob123 wrote:


I have created a sheet which is tracking 3 items per client and a number
of clients so I have set up a SUM function the only way I know how which
is a long a1+a4+a7, etc
Its works fine, but if I want to add a new client and 3 new rows, ALL of
the + after are now off.

Is there a better way to create a formula that will track say every 3rd
cell in a column but then let you lengthen or shorten the column?

Thanks!!

One way, by setting a range greater than your expected number of entries.
FirstRow is the first row number that you want to add. e.g. 1 for A1, A4, A7, etc
2 for A2, A5, A8, etc.

=SUMPRODUCT((MOD(ROW($A$1:$A$1000)-FirstRow,3)=0)*$A$1:$A$1000)

On Sat, 03 Mar 2012 21:19:48 -0500, Ron Rosenfeld wrote:

One way, by setting a range greater than your expected number of entries.
FirstRow is the first row number that you want to add. e.g. 1 for A1, A4, A7, etc
2 for A2, A5, A8, etc.

=SUMPRODUCT((MOD(ROW($A$1:$A$1000)-FirstRow,3)=0)*$A$1:$A$1000)

Actually, the above formula will only work if all of the entries are numeric. If some of the entries are text, then the above formula will give a VALUE error. To avoid that, you can use the following formula:


This formula must be **array-entered**:

=SUM(IF(MOD(ROW($A$1:$A$1000)-FirstRow,3)=0, $A$1:$A$1000))
----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.

"Ron Rosenfeld" wrote:
=SUMPRODUCT((MOD(ROW($A$1:$A$1000)-FirstRow,3)=0)*$A$1:$A$1000)

Actually, the above formula will only work if all of the entries
are numeric. If some of the entries are text, then the above
formula will give a VALUE error. To avoid that, you can use the
following formula:
This formula must be **array-entered**:
=SUM(IF(MOD(ROW($A$1:$A$1000)-FirstRow,3)=0, $A$1:$A$1000))

No need to abandon SUMPRODUCT and use the more complicated and error-prone
array-entered formula. Simply write:

=SUMPRODUCT(--(MOD(ROW($A$1:$A$1000)-FirstRow,3)=0),$A$1:$A$1000)

The double negative converts TRUE and FALSE to 1 and 0, which SUMPRODUCT
requires, as the multiplication did before.

On Sat, 3 Mar 2012 19:32:46 -0800, "joeu2004" wrote:

No need to abandon SUMPRODUCT. Simply write:

=SUMPRODUCT(--(MOD(ROW($A$1:$A$1000)-FirstRow,3)=0),$A$1:$A$1000)

The double negative converts TRUE and FALSE to 1 and 0, which SUMPRODUCT
requires, as the multiplication did before.

Of the behavior of the double unary I was well aware. What I did not realize is that:

=SUMPRODUCT(0*"x") -- #VALUE!
=SUMPRODUCT({0}*{"x"}) -- #VALUE!
=SUMPRODUCT(0,"x") -- #VALUE!

but

=SUMPRODUCT({0},{"x"}) -- 0

On Sat, 3 Mar 2012 19:32:46 -0800, "joeu2004" wrote:

"Ron Rosenfeld" wrote:
=SUMPRODUCT((MOD(ROW($A$1:$A$1000)-FirstRow,3)=0)*$A$1:$A$1000)

Actually, the above formula will only work if all of the entries
are numeric. If some of the entries are text, then the above
formula will give a VALUE error. To avoid that, you can use the
following formula:
This formula must be **array-entered**:
=SUM(IF(MOD(ROW($A$1:$A$1000)-FirstRow,3)=0, $A$1:$A$1000))

No need to abandon SUMPRODUCT and use the more complicated and error-prone
array-entered formula. Simply write:

=SUMPRODUCT(--(MOD(ROW($A$1:$A$1000)-FirstRow,3)=0),$A$1:$A$1000)

The double negative converts TRUE and FALSE to 1 and 0, which SUMPRODUCT
requires, as the multiplication did before.

Thank you for pointing that out. I was aware of the double unary behavior, but not of the behavior of SUMPRODUCT with the different factors as laid out in my previous message.

Thanks to everyone who answered. In theory I think I understand whats going on - which is that my range has names and numbers, however I thought that was the whole point of this formula, to track the sum of cells which are identified in some way by text in the same row?

Anyway, i am afraid these solutions might be a bit above my pay grade! Do you think someone might be able to produce the actual formula, and I will try to paste it? I have 24 columns so if I can get it to work once, I will try to rework it for the remaining columns. The colomn Constant is D and the numerics are F, then H, then L and so on. The rows go down to 406

Sales by Customer Summary - Annual
Jan 12 Jan 11
Units Units % Change

D F H
GROSS SALES
Store A
Model 1 1 1 0.0%
Model 2 3 3 0.0%
Model 3 5 3 66.7%
Store B
Model 1 5 2 150.0%
Model 2 2 1 100.0%
Model 3 1 2 -50.0%

The reason (please correct me if I am wrong) I want to change my current ++++++ formula is that I want to be able to add and subtract stores (rows) as needed.

Thanks Again