SUMPRODUCT using offset from ROW if X marks the spot

  • Thread starter Thread starter The Shaffer s
  • Start date Start date
T

The Shaffer s

I have a spreadsheet similar to the following:

Item, Total, 5, 13, 1, 127, 250
Lamps, formula, , X, , X,
Desks, formula, X, X, , ,
Tables, formula, , , X, , X

The formula results look on the Item row for corresponding X in the column
and add up the numbers:

Lamps, 140 ...
Desks, 18 ...
Tables, 251 ...

I managed to get a SUMPRODUCT formula to work using specific cell ranges.
What I can't get working, is the SUMPRODUCT formula using ROW()-xxx and
COLUMN()-yyy, so that the ranges are all relative. It's OK to know that the
Item row is 2 rows above the Desks row, for example.

With several spreadsheets in this format, I can then save as .csv files,
concatenate and reload as one into another spreadsheet and see in one sheet
the results of several.

Any help greatly appreciated.

Ken
 
You used specific cell addresses, I wanted a formula which uses ROW() and
COLUMN().
 
The Shaffer s said:
You used specific cell addresses, I wanted a formula which uses ROW() and
COLUMN().
Click to expand...

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])
 
Back
Top