Summing Returned Positions using MATCH
 

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

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

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

one way:

B5: =SUMPRODUCT(--(C5:L5=$O$1),COLUMN(INDIRECT("A:J")))


In article ,
"Sam via OfficeKB.com" wrote:

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

I didn't get the OP's values using this formula. I could with

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

but the OP only wanted 10 columns, so perhaps

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

would be better, or

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

to avoid problems if a column is inserted to the left.

I'm also not sure what the --(A5="x") is doing (since the OP didn't
mention column A), and I suspect that one should replace ="x" with =$O$1
in the above formulae to correspond with his MATCH() formula.



In article ,
"Bob Phillips" wrote:

Sam,

Try

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

Hi Bob,

Thank you for reply. I've tried the suggested Formula (below) but it does
not return the expected answer.

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

Further assistance appreciated.

Thanks
Sam

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

Hi JE McGimpsey,

Thank you for reply. Yor suggested Formulae return the expected results.

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

would be better, or

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

to avoid problems if a column is inserted to the left.

Much appreciated.

Thanks
Sam

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

Hi N Harkawat,

Thank you for reply. Your suggested Formula returns the expected results.

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"

Much appreciated.

Thanks
Sam

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

Thank you all for your time and assitance - not least Bob: for getting the
ball rolling. Your help is most appreciated.

Thanks
Sam

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

Also...

=SUM(IF(ISNUMBER(1/(C5:L5="x")),COLUMN(C5:L5)-COLUMN(C5)+1))

which needs to be confirmed with control+shift+enter instead of just
with enter. Might be quite expensive if copied to too many cells.

Sam via OfficeKB.com wrote:
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

Hi Aladin,

Thank you for reply. I didn't realise that there are so many variations to
provide the same result - it's all good stuff. I'm on a very steep learning
curve and all help is most definitely appreciated.

Also...

=SUM(IF(ISNUMBER(1/(C5:L5="x")),COLUMN(C5:L5)-COLUMN(C5)+1))

which needs to be confirmed with control+shift+enter instead of just
with enter. Might be quite expensive if copied to too many cells.


Thanks
Sam

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