# Sumifs returns 0 value

#### Erikku

##### New Member
Hi guys, would like to understand why is my formula giving me a zero value instead.

 A B C 1 date type Qty 2 01/06/2015 1025486687 3 3 02/06/2015 TM211587 1 4 02/06/2015 TM211588 2 5 02/06/2015 TM211589 5 6 02/06/2015 1025486687 6 7 06/06/2015 1025447587 12 8 07/06/2015 1025358579 1 9 07/06/2015 1025879687 1 10 07/06/2015 1025486687 4

<tbody>
</tbody>

FORMULA TO GET TOTAL QTY FOR TYPE "TM" ON 02/06/2015
This formula gives the correct result.
=SUMIFS(\$C2:\$C10,\$A2:\$A10,"02/06/2015",\$B2:\$B10,"TM*")

However for the following formula it returns a zero values instead of a value
=SUMIFS(\$C2:\$C10,\$A2:\$A10,"02/06/2015",\$B2:\$B10,"1*")

Hope the veteran here could provide some help on this. Thanks in advance.

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

#### liveinhope

##### Well-known Member
Excel sees The values in col "B" that start with a "1" as numbers not as text so doing a SUMIF on values that look like 1* doesn't work

One Solution is
-Insert a new column between B and C .

- enter this formula in C2 and drag down =TEXT(B2,"#####")

#### steve the fish

##### Well-known Member
SUMIF wont handle using a number with a wild card. You could use SUMPRODUCT.

=SUMPRODUCT((A2:A10="02/06/2015"+0)*(LEFT(B2:B10)="1")*C2:C10)

#### pgc01

##### MrExcel MVP
Hi

... or, maybe you don't need those values as number values, and simply convert all values in column B to text. This avoids the auxiliary column.

Replies
1
Views
342
Replies
6
Views
154
Replies
5
Views
268
Replies
1
Views
1K
Replies
3
Views
185

1,195,828
Messages
6,011,829
Members
441,650
Latest member
ceyoung75

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back