Excel Sumif with number

Question

=SUMIF(A1:A14, "102000*", B1:B14)

Why it cannot filter the value starts with 10200?

Answer 1

An array (CSE) formula solves the issue:

How it works:

• Suppose you want to add numbers like 1020001, then enter it as criteria in cell D74, and use this formula.

  {=SUM((B74:B82)*(--(A74:A82=D74)))}

• If you have more criteria, like I've shown in D74 & in E74, then use this one in C74.

  {=SUM((B74:B82)*(--(A74:A82=D74)+(--(A74:A82=E74))))}
  

N.B.

• Finish formula with Ctrl+Shift+Enter.

• You may adjust cell references in the formula as needed.

Answer 2

If you want to count the number of occurences, you can use

=SUMPRODUCT(--(--LEFT(A1:A7,5)=10200))

and if you actually want to sum values, use

=SUMPRODUCT(--(--LEFT(A1:A7,5)=10200)*(B1:B7))

Answer 3

You are better off creating a new column using the formula

left(a1,5)

Then base the sumif off that new column.