Sumifs returns 0 value

[Erikku]

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.

 

[liveinhope]

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,"#####")
- change your SUMIF to look at c$2:c$10 instead of B$2:B$10

 

[steve the fish]

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]

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.