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

[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]date[/TD]
[TD]store[/TD]
[TD]name[/TD]
[TD]item[/TD]
[TD]unit[/TD]
[/TR]
[TR]
[TD]22/1/2018[/TD]
[TD]east[/TD]
[TD]john[/TD]
[TD]tv[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1/2/2018[/TD]
[TD]south[/TD]
[TD]mary[/TD]
[TD]cell[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]1/3/2018[/TD]
[TD]east[/TD]
[TD]john[/TD]
[TD]pc[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]16/3/2018[/TD]
[TD]east[/TD]
[TD]john[/TD]
[TD]tv[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

 

[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
[TABLE="width: 391"]
<tbody>[TR]
[TD]date

[/TD]
[TD]store[/TD]
[TD]name[/TD]
[TD]item[/TD]
[TD]unit[/TD]
[/TR]
[TR]
[TD="align: right"]22-01-2018[/TD]
[TD]east[/TD]
[TD]john[/TD]
[TD]tv[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]01-02-2018[/TD]
[TD]south[/TD]
[TD]mary[/TD]
[TD]cell[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]01-03-2018[/TD]
[TD]east[/TD]
[TD]john[/TD]
[TD]pc[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]16-03-2018[/TD]
[TD]east[/TD]
[TD]john[/TD]
[TD]tv[/TD]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD="align: right"]16-03-2018[/TD]
[TD]east[/TD]
[TD]john[/TD]
[TD]tv[/TD]
[TD="align: right"]5
[/TD]
[/TR]
</tbody>[/TABLE]

=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 ?