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

[TABLE="width: 500"]
<tbody>[TR]
[TD]14012[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]15000[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]14999[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]16045[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]


=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 <thead> [TH="width: 10px, align: center"]Cell[/TH] [TH="align: left"]Formula[/TH] </thead><tbody> [TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] , align: center"]C2[/TH] [TD="align: left"]{=SUM( IF(LEFT(A1:A4,2)="14",B1:B4) )}

<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

[/TD]

</tbody>

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

Array Formulas <thead> [TH="width: 10px, align: center"]Cell[/TH] [TH="align: left"]Formula[/TH] </thead><tbody> [TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] , align: center"]C2[/TH] [TD="align: left"]{=SUM( IF(LEFT(A1:A4,2)="14",B1:B4) )}

<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

[/TD]

</tbody>

</body>

 

[bigdan]

Thanks a lot!

 

[shg]

Assuming the IDs are all 5-digit numbers,

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

14012​

[/td][td]

50​

[/td][td="bgcolor:#CCFFCC"]

150​

[/td][td="bgcolor:#CCFFCC"]C2: =SUMIFS(B2:B5, A2:A5, ">=14000", A2:A5, "<15000")[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

15000​

[/td][td]

1​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

14999​

[/td][td]

100​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

16045​

[/td][td]

3​

[/td][td][/td][td][/td][/tr]
[/table]

 

[DanteAmor]

You're welcome & thanks for the feedback.