On B5 type the following:-
=SUMPRODUCT(COLUMN(C5:L5)-2,IF(C5:L5=$O$1,1,0))
array entered (ctrl+shift+enter)
where cell O1 is where you have placed your "x"

and if you have hard coded "x" then instead of $o$1 in the formula simply
use "x"




"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