SUM help please

[ExcelNoob123]

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

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

Thanks!

 

[ExcelNoob123]

In searching her I have figured out it is the DSUM function that I m
needing. I have tried a number of times - seems simple enough but I can
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

 

[Ron Rosenfeld]

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)

 

[Ron Rosenfeld]

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.

 

[joeu2004]

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.

 

[Ron Rosenfeld]

No need to abandon SUMPRODUCT. Simply write:

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

 

[Ron Rosenfeld]

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.

 

[ExcelNoob123]

Thanks to everyone who answered. In theory I think I understand what
going on - which is that my range has names and numbers, however
thought that was the whole point of this formula, to track the sum o
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
will try to paste it? I have 24 columns so if I can get it to wor
once, I will try to rework it for the remaining columns. The colom
Constant is D and the numerics are F, then H, then L and so on. Th
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 curren
++++++ formula is that I want to be able to add and subtract store
(rows) as needed.

Thanks Again

;1532504']On Sat, 3 Mar 2012 19:32:46 -0800, "joeu2004

No need to abandon SUMPRODUCT and use the more complicated an 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, whic SUMPRODUCT
requires, as the multiplication did before.-

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