Sam,

Try

=--(A5="x")+SUMPRODUCT(--(C5:IV5="x"),COLUMN(C5:IV5))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Sam via OfficeKB.com" wrote in message
...
Hi All,

Can the MATCH Formula sum multiple positions and return the summed value
of
the positions?

I would like to return the position of cells that contain an x and have
the
positions added together to provide a total for each Row. The Formula
needs
to prevent error codes being returned where cells do not contain an x.

I have put an x in cell $O$1 so that I can reference the x as my lookup
value using the MATCH formula.

My data range consists of 100 Rows and 10 Columns from Row 5 Columnn C to
Row 104 Column L. Each Row consists of 10 Columns.

Within my data Range Column C is my 1st column, Column D is my 2nd column,
Column E my 3rd column etc.

The answer for each Row will be returned in their respective Row, Column
B.

Example:
Column No. 1 2 3 4 5 6 7 8 9 10
Columns C D E F G H I J K L
Row 5 x x x
Row 6 x x

Expected Result:
The answer returned in cell B5 should be 12: 1+5+6=12
The answer returned in cell B6 should be 19: 9+10=19

Alignment of x's in above example:

The x's in Row 5 are in Columns C, G and H.
The x's in Row 6 are in Columns K and L.

Regards,
Sam

--
Message posted via http://www.officekb.com