Formula to sum values based on an account number match but with spaces at the beginning of the cell

[danielv1985]

Hi all,

First time poster and really appreciate any help.

I'm struggling to compose the formula to sum the total based on an account character match.

The text in B176 has 5 spaces before the digits commence.

I need to sum the values from the below table (same tab) but the digits commence at the beginning of the cell i.e. no spaces

Note, only the digits match - not the subsequent description.

Thank you,
Daniel

 

[Bosquedeguate]

Column B appears to start with the "product number". If leading spaces are causing a problem use the TRIM function.
Please provide your formula from you cells or columns where the product number with leading spaces is causing the problem

=TRIM("B176") should fix your problem

your modified function should be something like the following
=SUM( .....TRIM("B176") ...)

Again if you provide your actual formula I can give you a complete formula fix.

 

[Bosquedeguate]

To clarify my response
Replace references to your product like
B176
with
TRIM(B176)

 

[kevin9999]

Something like this:

Book1
	B	C
175	Telecommunications
176
177	66214001 - telephone	2,812
Sheet1

Cell Formulas
Range	Formula
C177	C177	=SUMIF($B$227:$B$231,LEFT(TRIM(B177),8)&"*",$C$227:$C$231)

 

[danielv1985]

Something like this:

Book1
	B	C
175	Telecommunications
176
177	66214001 - telephone	2,812
Sheet1

Cell Formulas
Range	Formula
C177	C177	=SUMIF($B$227:$B$231,LEFT(TRIM(B177),8)&"*",$C$227:$C$231)

Thank you @kevin9999

 

[kevin9999]

Thank you @kevin9999

You're welcome, and thanks for the feedback