sumifs() and data validation situation

[lezawang]

Hi
I have table like below, the moth column is =month(a2)

select name from the list: k4 = john
select item from the list: k5 = tv
select date: k6 = 18/12/2017 (this list is from date column, but the cell is custom formatted as MM)


L2 =SUMIFS(E2:E125,C2:C125,K4,D2:D125,K5,F2:F125,k6)

The above function would return 0. The problem is with K6 which is shown now 12. If I use another cell lets L6 and enter 12, I got the right result


[TABLE="width: 395"]
<colgroup><col><col span="5"></colgroup><tbody>[TR]
[TD]date[/TD]
[TD]store[/TD]
[TD]name[/TD]
[TD]item[/TD]
[TD]unit[/TD]
[TD]month[/TD]
[/TR]
[TR]
[TD="align: right"]18/12/2017[/TD]
[TD]east[/TD]
[TD]john[/TD]
[TD]tv[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]28/06/2018[/TD]
[TD]east[/TD]
[TD]john[/TD]
[TD]computer[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]05/09/2018[/TD]
[TD]west[/TD]
[TD]david[/TD]
[TD]dryer[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]21/07/2018[/TD]
[TD]west[/TD]
[TD]mary[/TD]
[TD]washer[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]28/02/2018[/TD]
[TD]south[/TD]
[TD]alex[/TD]
[TD]tv[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Try
=SUMIFS(E2:E125,C2:C125,K4,D2:D125,K5,F2:F125,MONTH(K6))

 

[lezawang]

wow, thank you very much.

 

[Fluff]

You're welcome