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

date	store	name	item	unit	month
18/12/2017	east	john	tv	6	12
28/06/2018	east	john	computer	3	6
05/09/2018	west	david	dryer	18	9
21/07/2018	west	mary	washer	18	7
28/02/2018	south	alex	tv	1	2

<colgroup><col><col span="5"></colgroup><tbody>
</tbody>

 

[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