You used specific cell addresses, I wanted a formula which uses ROW() and
COLUMN().

On 11/1/04 10:41 PM, in article ,
"MikeY" wrote:

B2: =SUMPRODUCT(C$1:G$1*(C2:G2="X"))

"The Shaffer s" wrote...
You used specific cell addresses, I wanted a formula which uses ROW() and
COLUMN().

"MikeY" wrote:
B2: =SUMPRODUCT(C$1:G$1*(C2:G2="X"))

If as you say in your original post that it's OK to know that the Items row
is 2 above the Desks row, then by implication the relative position of the
Items row is always know in relation to the other rows. If you understand
what this means and do mean it, then if the Items row were B3:H3, the
formulas would be

Lamps: =SUMIF(D4:H4,"X",D3:H3)
Desks: =SUMIF(D5:H5,"X",D3:H3)
Tables: =SUMIF(D6:H6,"X",D3:H3)

On the other hand, if you think that you need COLUMN or ROW or OFFSET, then
you either don't understand the problem or haven't stated all your specs. To
see this is so, rewrite the formulas above in R1C1 references.

Lamps: =SUMIF(RC[1]:RC[5],"X",R[-1]C[1]:R[-1]C[5])
Desks: =SUMIF(RC[1]:RC[5],"X",R[-2]C[1]:R[-2]C[5])
Tables: =SUMIF(RC[1]:RC[5],"X",R[-3]C[1]:R[-3]C[5])