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
Quote:
Originally Posted by Ron Rosenfeld[_2_]
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.
|