Sum/Average Based on Dates Criteria

M

manishc1989

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

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.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[TD]
D​
[/TD]
[TD]
E​
[/TD]
[TD]
F​
[/TD]
[TD]
G​
[/TD]
[TD]
H​
[/TD]
[TD]
I​
[/TD]
[TD]
J​
[/TD]
[TD]
K​
[/TD]
[TD]
L​
[/TD]
[TD]
M​
[/TD]
[TD]
N​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD]Employee NAME[/TD]
[TD]Metrics 1[/TD]
[TD]Metrics 2[/TD]
[TD]Metrics 3[/TD]
[TD]Metrics 4[/TD]
[TD]Metrics 5[/TD]
[TD]Metrics 6[/TD]
[TD]Metrics 7[/TD]
[TD]Metrics 8[/TD]
[TD]Metrics 9[/TD]
[TD]Metrics 10[/TD]
[TD]Metrics 11[/TD]
[TD]date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]Mr A[/TD]
[TD]
1753​
[/TD]
[TD]
2946​
[/TD]
[TD]
2457​
[/TD]
[TD]
4808​
[/TD]
[TD]
2334​
[/TD]
[TD]
2929​
[/TD]
[TD]
4649​
[/TD]
[TD]
4543​
[/TD]
[TD]
3419​
[/TD]
[TD]
3471​
[/TD]
[TD]
3520​
[/TD]
[TD]
5/29/2018​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]Mr B[/TD]
[TD]
4549​
[/TD]
[TD]
3107​
[/TD]
[TD]
3114​
[/TD]
[TD]
4491​
[/TD]
[TD]
1024​
[/TD]
[TD]
2090​
[/TD]
[TD]
2097​
[/TD]
[TD]
1262​
[/TD]
[TD]
1412​
[/TD]
[TD]
3310​
[/TD]
[TD]
3645​
[/TD]
[TD]
5/29/2018​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]Mr C[/TD]
[TD]
4975​
[/TD]
[TD]
3346​
[/TD]
[TD]
2577​
[/TD]
[TD]
3312​
[/TD]
[TD]
3463​
[/TD]
[TD]
2044​
[/TD]
[TD]
3056​
[/TD]
[TD]
3724​
[/TD]
[TD]
4978​
[/TD]
[TD]
1190​
[/TD]
[TD]
3531​
[/TD]
[TD]
5/29/2018​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD]Mr P[/TD]
[TD]
2644​
[/TD]
[TD]
3632​
[/TD]
[TD]
1615​
[/TD]
[TD]
2895​
[/TD]
[TD]
4824​
[/TD]
[TD]
1240​
[/TD]
[TD]
1161​
[/TD]
[TD]
2871​
[/TD]
[TD]
2667​
[/TD]
[TD]
3235​
[/TD]
[TD]
3233​
[/TD]
[TD]
5/30/2018​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
6​
[/TD]
[TD]Mr B[/TD]
[TD]
2775​
[/TD]
[TD]
2738​
[/TD]
[TD]
4484​
[/TD]
[TD]
4732​
[/TD]
[TD]
1025​
[/TD]
[TD]
3979​
[/TD]
[TD]
4068​
[/TD]
[TD]
2759​
[/TD]
[TD]
1760​
[/TD]
[TD]
3886​
[/TD]
[TD]
4893​
[/TD]
[TD]
5/30/2018​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
7​
[/TD]
[TD]Mr C[/TD]
[TD]
1987​
[/TD]
[TD]
3154​
[/TD]
[TD]
3130​
[/TD]
[TD]
2889​
[/TD]
[TD]
1088​
[/TD]
[TD]
1927​
[/TD]
[TD]
4714​
[/TD]
[TD]
4015​
[/TD]
[TD]
4787​
[/TD]
[TD]
3021​
[/TD]
[TD]
1427​
[/TD]
[TD]
5/30/2018​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
8​
[/TD]
[TD]Mr A[/TD]
[TD]
4709​
[/TD]
[TD]
3037​
[/TD]
[TD]
4518​
[/TD]
[TD]
1639​
[/TD]
[TD]
4310​
[/TD]
[TD]
2283​
[/TD]
[TD]
2055​
[/TD]
[TD]
3399​
[/TD]
[TD]
2567​
[/TD]
[TD]
2997​
[/TD]
[TD]
1297​
[/TD]
[TD]
5/31/2018​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
9​
[/TD]
[TD]Mr P[/TD]
[TD]
4182​
[/TD]
[TD]
1372​
[/TD]
[TD]
2991​
[/TD]
[TD]
1707​
[/TD]
[TD]
3861​
[/TD]
[TD]
2793​
[/TD]
[TD]
3972​
[/TD]
[TD]
2138​
[/TD]
[TD]
2497​
[/TD]
[TD]
2694​
[/TD]
[TD]
4696​
[/TD]
[TD]
5/31/2018​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
10​
[/TD]
[TD]Mr C[/TD]
[TD]
4295​
[/TD]
[TD]
1984​
[/TD]
[TD]
3096​
[/TD]
[TD]
1578​
[/TD]
[TD]
3354​
[/TD]
[TD]
1006​
[/TD]
[TD]
3452​
[/TD]
[TD]
1575​
[/TD]
[TD]
3212​
[/TD]
[TD]
4612​
[/TD]
[TD]
4047​
[/TD]
[TD]
5/31/2018​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
11​
[/TD]
[TD]Mr A[/TD]
[TD]
2946​
[/TD]
[TD]
3916​
[/TD]
[TD]
3697​
[/TD]
[TD]
2100​
[/TD]
[TD]
1715​
[/TD]
[TD]
1921​
[/TD]
[TD]
4909​
[/TD]
[TD]
3709​
[/TD]
[TD]
2666​
[/TD]
[TD]
3274​
[/TD]
[TD]
4806​
[/TD]
[TD]
6/1/2018​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
12​
[/TD]
[TD]Mr P[/TD]
[TD]
3577​
[/TD]
[TD]
1538​
[/TD]
[TD]
4680​
[/TD]
[TD]
2590​
[/TD]
[TD]
4231​
[/TD]
[TD]
1107​
[/TD]
[TD]
4588​
[/TD]
[TD]
1955​
[/TD]
[TD]
2918​
[/TD]
[TD]
4329​
[/TD]
[TD]
3841​
[/TD]
[TD]
6/1/2018​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
13​
[/TD]
[TD]Mr C[/TD]
[TD]
1426​
[/TD]
[TD]
1373​
[/TD]
[TD]
3536​
[/TD]
[TD]
2065​
[/TD]
[TD]
4690​
[/TD]
[TD]
3334​
[/TD]
[TD]
1840​
[/TD]
[TD]
1985​
[/TD]
[TD]
2878​
[/TD]
[TD]
2201​
[/TD]
[TD]
1120​
[/TD]
[TD]
6/1/2018​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
14​
[/TD]
[TD]Mr A[/TD]
[TD]
2894​
[/TD]
[TD]
2062​
[/TD]
[TD]
1567​
[/TD]
[TD]
3363​
[/TD]
[TD]
3920​
[/TD]
[TD]
3612​
[/TD]
[TD]
4654​
[/TD]
[TD]
3933​
[/TD]
[TD]
3270​
[/TD]
[TD]
2555​
[/TD]
[TD]
4540​
[/TD]
[TD]
6/2/2018​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
15​
[/TD]
[TD]Mr B[/TD]
[TD]
4702​
[/TD]
[TD]
4031​
[/TD]
[TD]
1352​
[/TD]
[TD]
3386​
[/TD]
[TD]
1706​
[/TD]
[TD]
1835​
[/TD]
[TD]
4688​
[/TD]
[TD]
3915​
[/TD]
[TD]
4663​
[/TD]
[TD]
3373​
[/TD]
[TD]
2439​
[/TD]
[TD]
6/2/2018​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
16​
[/TD]
[TD]Mr C[/TD]
[TD]
2159​
[/TD]
[TD]
4691​
[/TD]
[TD]
4033​
[/TD]
[TD]
4949​
[/TD]
[TD]
3514​
[/TD]
[TD]
2612​
[/TD]
[TD]
3537​
[/TD]
[TD]
3056​
[/TD]
[TD]
3467​
[/TD]
[TD]
4007​
[/TD]
[TD]
3137​
[/TD]
[TD]
6/2/2018​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
17​
[/TD]
[TD]Mr W[/TD]
[TD]
2756​
[/TD]
[TD]
3793​
[/TD]
[TD]
3901​
[/TD]
[TD]
2969​
[/TD]
[TD]
4535​
[/TD]
[TD]
2874​
[/TD]
[TD]
3754​
[/TD]
[TD]
3920​
[/TD]
[TD]
2239​
[/TD]
[TD]
4163​
[/TD]
[TD]
3831​
[/TD]
[TD]
6/2/2018​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
18​
[/TD]
[TD]Mr A[/TD]
[TD]
3590​
[/TD]
[TD]
3407​
[/TD]
[TD]
3795​
[/TD]
[TD]
1056​
[/TD]
[TD]
2189​
[/TD]
[TD]
2128​
[/TD]
[TD]
1984​
[/TD]
[TD]
4804​
[/TD]
[TD]
2983​
[/TD]
[TD]
3514​
[/TD]
[TD]
3775​
[/TD]
[TD]
6/3/2018​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
19​
[/TD]
[TD]Mr P[/TD]
[TD]
3680​
[/TD]
[TD]
4345​
[/TD]
[TD]
2232​
[/TD]
[TD]
1560​
[/TD]
[TD]
2941​
[/TD]
[TD]
2357​
[/TD]
[TD]
4133​
[/TD]
[TD]
4838​
[/TD]
[TD]
1456​
[/TD]
[TD]
4803​
[/TD]
[TD]
2909​
[/TD]
[TD]
6/3/2018​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
20​
[/TD]
[TD]Mr C[/TD]
[TD]
4402​
[/TD]
[TD]
1457​
[/TD]
[TD]
4485​
[/TD]
[TD]
1041​
[/TD]
[TD]
3346​
[/TD]
[TD]
1939​
[/TD]
[TD]
2489​
[/TD]
[TD]
3129​
[/TD]
[TD]
3465​
[/TD]
[TD]
1661​
[/TD]
[TD]
4393​
[/TD]
[TD]
6/3/2018​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
21​
[/TD]
[TD]Mr Z[/TD]
[TD]
2279​
[/TD]
[TD]
2568​
[/TD]
[TD]
2477​
[/TD]
[TD]
4238​
[/TD]
[TD]
4720​
[/TD]
[TD]
4299​
[/TD]
[TD]
4279​
[/TD]
[TD]
2174​
[/TD]
[TD]
3250​
[/TD]
[TD]
3540​
[/TD]
[TD]
1891​
[/TD]
[TD]
6/3/2018​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
22​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
23​
[/TD]
[TD][/TD]
[TD]
5/29/2018​
[/TD]
[TD]
5/30/2018​
[/TD]
[TD]
5/31/2018​
[/TD]
[TD][/TD]
[TD]
May-18​
[/TD]
[TD]
Jun-18​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
24​
[/TD]
[TD]Metrics 1[/TD]
[TD]
11277​
[/TD]
[TD]
7406​
[/TD]
[TD]
13186​
[/TD]
[TD][/TD]
[TD]
31869​
[/TD]
[TD]
34411​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
25​
[/TD]
[TD]Metrics 2[/TD]
[TD]
9399​
[/TD]
[TD]
9524​
[/TD]
[TD]
6393​
[/TD]
[TD][/TD]
[TD]
25316​
[/TD]
[TD]
33181​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
26​
[/TD]
[TD]Metrics 3[/TD]
[TD]
8148​
[/TD]
[TD]
9229​
[/TD]
[TD]
10605​
[/TD]
[TD][/TD]
[TD]
27982​
[/TD]
[TD]
35755​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
27​
[/TD]
[TD]Metrics 4[/TD]
[TD]
12611​
[/TD]
[TD]
10516​
[/TD]
[TD]
4924​
[/TD]
[TD][/TD]
[TD]
28051​
[/TD]
[TD]
29317​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
28​
[/TD]
[TD]Metrics 5[/TD]
[TD]
6821​
[/TD]
[TD]
6937​
[/TD]
[TD]
11525​
[/TD]
[TD][/TD]
[TD]
25283​
[/TD]
[TD]
37507​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
29​
[/TD]
[TD]Metrics 6[/TD]
[TD]
7063​
[/TD]
[TD]
7146​
[/TD]
[TD]
6082​
[/TD]
[TD][/TD]
[TD]
20291​
[/TD]
[TD]
28018​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
30​
[/TD]
[TD]Metrics 7[/TD]
[TD]
9802​
[/TD]
[TD]
9943​
[/TD]
[TD]
9479​
[/TD]
[TD][/TD]
[TD]
29224​
[/TD]
[TD]
40855​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
31​
[/TD]
[TD]Metrics 8[/TD]
[TD]
9529​
[/TD]
[TD]
9645​
[/TD]
[TD]
7112​
[/TD]
[TD][/TD]
[TD]
26286​
[/TD]
[TD]
37418​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
32​
[/TD]
[TD]Metrics 9[/TD]
[TD]
9809​
[/TD]
[TD]
9214​
[/TD]
[TD]
8276​
[/TD]
[TD][/TD]
[TD]
27299​
[/TD]
[TD]
33255​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
33​
[/TD]
[TD]Metrics 10[/TD]
[TD]
7971​
[/TD]
[TD]
10142​
[/TD]
[TD]
10303​
[/TD]
[TD][/TD]
[TD]
28416​
[/TD]
[TD]
37420​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
34​
[/TD]
[TD]Metrics 11[/TD]
[TD]
10696​
[/TD]
[TD]
9553​
[/TD]
[TD]
10040​
[/TD]
[TD][/TD]
[TD]
30289​
[/TD]
[TD]
36682​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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


 
Upvote 0
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.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Weighted scores with some weightings the full 100%
Replies
7
Views
279
etaf
etaf
A
  • Locked
  • Question Question
Look up two rows then return the metric for that selection.
Replies
1
Views
309
Fluff
Fluff
M
  • Question Question
Copy values from row to another sheet matched to todays date
Replies
1
Views
458
Herakles
H
JenniferMurphy
  • Solved
How to count the number of numbers in a given period?
2
Replies
16
Views
314
JenniferMurphy
JenniferMurphy
B
  • Solved
Counting as one when one unique customer purchase item with multiple time in a month
Replies
1
Views
414
Eric W
Eric W

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top