Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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)) |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
#11
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Look up data in colum a and find match in colum b | Excel Discussion (Misc queries) | |||
Look up data in colum a and find match in colum b | Excel Discussion (Misc queries) | |||
summing data that match critieria in a rnage | Excel Discussion (Misc queries) | |||
Find a match that;s not exact | Excel Worksheet Functions | |||
Vlookup, Index & Match | Excel Worksheet Functions |