sumifs() with date

[lezawang]

Hi
I want to use sumifs() function to find out how many tv John sold for month of March. Is that possible?
I can find out how many tv john sold like this

=sumifs(E1:E10,C1:C10,"john",D1:D10,"tv")

But now I want to find out for month of March. Thank you very much

date	store	name	item	unit
22/1/2018	east	john	tv	2
1/2/2018	south	mary	cell	4
1/3/2018	east	john	pc	10
16/3/2018	east	john	tv	4

<tbody>
</tbody>

 

[Aladin Akyurek]

Like this for example...

=SUMIFS($E$2:$E$10,$C$2:$C$10,"john",$D$2:$D$10,"tv",$A$2:$A$10,">="&DATE(2018,3,1),$A$2:$A$10,"<="&EOMONTH(DATE(2018,3,1),0))

 

[r1998]

Hello lezawang
data is from j4 to n8
use this formula n press ctrl + shift + enter

=INDEX(N5:N8,MATCH(3&L5&M5,MONTH(J5:J8)&L5:L8&M5:M8,0))

answer will be 4

 

[r1998]

my earlier answer is wrong, it will not sum,
but this will,
use with ctrl + shift + enter
data is from j4 to n9

date	store	name	item	unit
22-01-2018	east	john	tv	2
01-02-2018	south	mary	cell	4
01-03-2018	east	john	pc	10
16-03-2018	east	john	tv	4
16-03-2018	east	john	tv	5

<tbody>
</tbody>

=SUM(IF(MONTH(J5:J9)=3,IF(L5:L9=L5,IF(M5:M9=M5,N5:N9))))

answer will be 9

 

[Aladin Akyurek]

See post #2 for a more efficient set up...

 

[admiral100]

Hi.

Why not use a pivot table ?