sumif for all numbers starting with a couple digits

[bigdan]

I want to get the sum of all numbers in col b when the ID in col a starts with 14.

So for example

14012	50
15000	1
14999	100
16045	3

<tbody>
</tbody>

=SUMIF(a:a,"14*",b:b)

I'm getting a zero.

My formula seems to work for text in col A but not for numbers.

Help please!

 

[CyrusTheVirus]

could try something like the below:

=SUMPRODUCT($B$1:$B$4,--(--LEFT($A$1:$A$4,2)=14))

 

[DanteAmor]

Try:

=SUMPRODUCT((LEFT(A1:A4,2)="14")*(B1:B4))

Or:

Book1
	A	B	C
1	14012	50
2	15000	1	150
3	14999	100
4	16045	3
Hoja1

Cell Formulas
Range	Formula
C2	{=SUM(IF(LEFT(A1:A4,2)="14",B1:B4))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[bigdan]

Thanks guys. Would a SUMIF not work? That would be so much simpler than a sumproduct

 

[DanteAmor]

Sumif can not check a text string.
If you do not want sumproduct, there is the option of SUM(IF

Array Formulas Cell Formula C2 {=SUM(IF(LEFT(A1:A4,2)="14",B1:B4))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

****** id="cke_pastebin" style="position: absolute; top: 23.4517px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

Array Formulas Cell Formula C2 {=SUM(IF(LEFT(A1:A4,2)="14",B1:B4))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

</body>

 

[bigdan]

Thanks a lot!

 

[shg]

Assuming the IDs are all 5-digit numbers,

	A​	B​	C​	D​
2​	14012​	50​	150​	C2: =SUMIFS(B2:B5, A2:A5, ">=14000", A2:A5, "<15000")
3​	15000​	1​
4​	14999​	100​
5​	16045​	3​

 

[DanteAmor]

You're welcome & thanks for the feedback.