Sum/Average Based on Dates Criteria

[manishc1989]

I pull the below report from our internal application. It gives the performance of various employees based on different metrics. Data is pulled for minimum 60 days time for which I need to sum up and average by dates of all individual metrics such as Metrics 1 Performance on a particular day and month such as 05/29/2018 is total of 11277 and May month 31869 total.

Group	Sales_Group
Dates:	5/29/2018
Employee NAME	Metrics 1	Metrics 2	Metrics 3	Metrics 4	Metrics 5	Metrics 6	Metrics 7	Metrics 8	Metrics 9	Metrics 10	Metrics 11
Mr A	1753	2946	2457	4808	2334	2929	4649	4543	3419	3471	3520
Mr B	4549	3107	3114	4491	1024	2090	2097	1262	1412	3310	3645
Mr C	4975	3346	2577	3312	3463	2044	3056	3724	4978	1190	3531
Dates:	5/30/2018
Employee NAME	Metrics 1	Metrics 2	Metrics 3	Metrics 4	Metrics 5	Metrics 6	Metrics 7	Metrics 8	Metrics 9	Metrics 10	Metrics 11
Mr P	2644	3632	1615	2895	4824	1240	1161	2871	2667	3235	3233
Mr B	2775	2738	4484	4732	1025	3979	4068	2759	1760	3886	4893
Mr C	1987	3154	3130	2889	1088	1927	4714	4015	4787	3021	1427
Dates:	5/31/2018
Employee NAME	Metrics 1	Metrics 2	Metrics 3	Metrics 4	Metrics 5	Metrics 6	Metrics 7	Metrics 8	Metrics 9	Metrics 10	Metrics 11
Mr A	4709	3037	4518	1639	4310	2283	2055	3399	2567	2997	1297
Mr P	4182	1372	2991	1707	3861	2793	3972	2138	2497	2694	4696
Mr C	4295	1984	3096	1578	3354	1006	3452	1575	3212	4612	4047
Dates:	6/1/2018
Employee NAME	Metrics 1	Metrics 2	Metrics 3	Metrics 4	Metrics 5	Metrics 6	Metrics 7	Metrics 8	Metrics 9	Metrics 10	Metrics 11
Mr A	2946	3916	3697	2100	1715	1921	4909	3709	2666	3274	4806
Mr P	3577	1538	4680	2590	4231	1107	4588	1955	2918	4329	3841
Mr C	1426	1373	3536	2065	4690	3334	1840	1985	2878	2201	1120
Dates:	6/2/2018
Employee NAME	Metrics 1	Metrics 2	Metrics 3	Metrics 4	Metrics 5	Metrics 6	Metrics 7	Metrics 8	Metrics 9	Metrics 10	Metrics 11
Mr A	2894	2062	1567	3363	3920	3612	4654	3933	3270	2555	4540
Mr B	4702	4031	1352	3386	1706	1835	4688	3915	4663	3373	2439
Mr C	2159	4691	4033	4949	3514	2612	3537	3056	3467	4007	3137
Mr W	2756	3793	3901	2969	4535	2874	3754	3920	2239	4163	3831
Dates:	5/3/2018
Employee NAME	Metrics 1	Metrics 2	Metrics 3	Metrics 4	Metrics 5	Metrics 6	Metrics 7	Metrics 8	Metrics 9	Metrics 10	Metrics 11
Mr A	3590	3407	3795	1056	2189	2128	1984	4804	2983	3514	3775
Mr P	3680	4345	2232	1560	2941	2357	4133	4838	1456	4803	2909
Mr C	4402	1457	4485	1041	3346	1939	2489	3129	3465	1661	4393
Mr Z	2279	2568	2477	4238	4720	4299	4279	2174	3250	3540	1891

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

Required Output:

	6/4/2018	6/3/2018	5/2/2018 and so on	May 2018	June 2018
Metrics 1 <tbody> </tbody>
Metrics 2 <tbody> </tbody>
Metrics 3 <tbody> </tbody>

<tbody>
</tbody>

Data is always pulled in the same format and dates are in ascending order.

I want perform this activity using Excel formulas because I have a restriction of using any macros on this file.
I have used few combination by using SUM(OFFSET('Final Figures'!$BI$1,MATCH(E5,'Final Figures'!$BI:$BI,0). But didn't get the desired results.

Please provide the possible solution.

 

[MARZIOTULLIO]

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

1​

Employee NAME

Metrics 1

Metrics 2

Metrics 3

Metrics 4

Metrics 5

Metrics 6

Metrics 7

Metrics 8

Metrics 9

Metrics 10

Metrics 11

date

2​

Mr A

1753​

2946​

2457​

4808​

2334​

2929​

4649​

4543​

3419​

3471​

3520​

5/29/2018​

3​

Mr B

4549​

3107​

3114​

4491​

1024​

2090​

2097​

1262​

1412​

3310​

3645​

5/29/2018​

4​

Mr C

4975​

3346​

2577​

3312​

3463​

2044​

3056​

3724​

4978​

1190​

3531​

5/29/2018​

5​

Mr P

2644​

3632​

1615​

2895​

4824​

1240​

1161​

2871​

2667​

3235​

3233​

5/30/2018​

6​

Mr B

2775​

2738​

4484​

4732​

1025​

3979​

4068​

2759​

1760​

3886​

4893​

5/30/2018​

7​

Mr C

1987​

3154​

3130​

2889​

1088​

1927​

4714​

4015​

4787​

3021​

1427​

5/30/2018​

8​

Mr A

4709​

3037​

4518​

1639​

4310​

2283​

2055​

3399​

2567​

2997​

1297​

5/31/2018​

9​

Mr P

4182​

1372​

2991​

1707​

3861​

2793​

3972​

2138​

2497​

2694​

4696​

5/31/2018​

10​

Mr C

4295​

1984​

3096​

1578​

3354​

1006​

3452​

1575​

3212​

4612​

4047​

5/31/2018​

11​

Mr A

2946​

3916​

3697​

2100​

1715​

1921​

4909​

3709​

2666​

3274​

4806​

6/1/2018​

12​

Mr P

3577​

1538​

4680​

2590​

4231​

1107​

4588​

1955​

2918​

4329​

3841​

6/1/2018​

13​

Mr C

1426​

1373​

3536​

2065​

4690​

3334​

1840​

1985​

2878​

2201​

1120​

6/1/2018​

14​

Mr A

2894​

2062​

1567​

3363​

3920​

3612​

4654​

3933​

3270​

2555​

4540​

6/2/2018​

15​

Mr B

4702​

4031​

1352​

3386​

1706​

1835​

4688​

3915​

4663​

3373​

2439​

6/2/2018​

16​

Mr C

2159​

4691​

4033​

4949​

3514​

2612​

3537​

3056​

3467​

4007​

3137​

6/2/2018​

17​

Mr W

2756​

3793​

3901​

2969​

4535​

2874​

3754​

3920​

2239​

4163​

3831​

6/2/2018​

18​

Mr A

3590​

3407​

3795​

1056​

2189​

2128​

1984​

4804​

2983​

3514​

3775​

6/3/2018​

19​

Mr P

3680​

4345​

2232​

1560​

2941​

2357​

4133​

4838​

1456​

4803​

2909​

6/3/2018​

20​

Mr C

4402​

1457​

4485​

1041​

3346​

1939​

2489​

3129​

3465​

1661​

4393​

6/3/2018​

21​

Mr Z

2279​

2568​

2477​

4238​

4720​

4299​

4279​

2174​

3250​

3540​

1891​

6/3/2018​

22​

23​

5/29/2018​

5/30/2018​

5/31/2018​

May-18​

Jun-18​

24​

Metrics 1

11277​

7406​

13186​

31869​

34411​

25​

Metrics 2

9399​

9524​

6393​

25316​

33181​

26​

Metrics 3

8148​

9229​

10605​

27982​

35755​

27​

Metrics 4

12611​

10516​

4924​

28051​

29317​

28​

Metrics 5

6821​

6937​

11525​

25283​

37507​

29​

Metrics 6

7063​

7146​

6082​

20291​

28018​

30​

Metrics 7

9802​

9943​

9479​

29224​

40855​

31​

Metrics 8

9529​

9645​

7112​

26286​

37418​

32​

Metrics 9

9809​

9214​

8276​

27299​

33255​

33​

Metrics 10

7971​

10142​

10303​

28416​

37420​

34​

Metrics 11

10696​

9553​

10040​

30289​

36682​

<tbody>
</tbody>

Change file in this way

B24=SUMPRODUCT(($B$1:$L$1=$A24)*($B$2:$L$21)*($M$2:$M$21=B$23)) copy down and across

F24 =SUMPRODUCT(($B$1:$L$1=$A24)*($B$2:$L$21)*(MONTH($M$2:$M$21)=MONTH(F$23))) copy down and across

 

[manishc1989]

Thanks Marziotullio for this excellent solution using Sumproduct.But there is an another problem, actually file contains more than 50000 records and if I manually copy and paste the dates of each day, it will be very time consuming.
Can we simply take the reference of lookup date in the source data and sum up the records of any particular metrics, without making any changes in the original format.
Please suggest.