Summing Returned Positions using MATCH

[Sam via OfficeKB.com]

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

 

[Bob Phillips]

Sam,

Try

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

--

HTH

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

 

[N Harkawat]

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"

 

[JE McGimpsey]

one way:

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

 

[JE McGimpsey]

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.

 

[Sam via OfficeKB.com]

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

 

[Sam via 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

 

[Sam via 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

 

[Sam via 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

 

[Aladin Akyurek]

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]

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