Dates to month with if conditions

[Vishaal]

Hi All,

Thanks in advance,

we have the following sheets

sheet1

Time and date	Email Address	Option	test
5-5-2020 12:52:30	goldy@zyx.com	call1
5-5-2020 12:58:38	vin@fdfs.com	call 2
5-5-2020 13:14:23	pate@dfd.com	call1	Option 1
5-5-2020 13:15:34	lucy@fdfd.com	call3
5-5-2020 13:25:03	goldy@zyx.com	call2
6-9-2020 10:38:38	vin@fdfs.com	call
6-9-2020 16:29:31	pate@dfd.com	call1
6-9-2020 13:37:21	lucy@fdfd.com	call 2
6-9-2020 17:19:38	goldy@zyx.com	call1
6-9-2020 17:22:23	vin@fdfs.com	call3
6-9-2020 17:43:12	pate@dfd.com	jcall 5

sheet2

call 1
Team	Name	EMail id	01-April-2020	02-April-2020	02-May-2020	04-June-2020
India	Goldy	goldy@zyx.com	15	20	2	1
India	Vina	vin@fdfs.com	2	15	20	0
Newzeland	Pate	pate@dfd.com	0	2	6	8
Newzeland	Lucy	lucy@fdfd.com	3	4	8	10

sheet3

			May 2020
			call 1	call 2	call 3	call 4
India	Goldy	goldy@zyx.com
India	Vina	vin@fdfs.com
Newzeland	Pate	pate@dfd.com
Newzeland	Lucy	lucy@fdfd.com

we want to do following in sheet 3,
E4, F4, G4, H4

total no. of count if from sheet3 the value E3 and B4 and C4 and D4 and E2 will match then count from sheet1 and show the result, in sheet1 we have dates no month.

can we use it as array formula, if possible

 

[gaz_chops]

Try

=SUMPRODUCT((Sheet1!$B$2:$B$12=Sheet3!$C3)*(Sheet1!$C$2:$C$12=Sheet3!D$2)*(TEXT(Sheet1!$A$2:$A$12,"mmm")=TEXT(Sheet3!$D$1,"mmm")))

You may need to change some of the references