formula sumifs data from sheet to another

A

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
1,494
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
i would formula by sumif function i would sum data relevant a7,a8 the result in d7,d8 based on month the choose month in a2
MMM.xlsx
ABCDEF
3Customer CodeMarketICMonthYearGross sales Volume
43176ExportYJan2020 49.23
53425ExportYJan2020 31.83
63950ExportNJan2020 2.15
73425ExportYJan2020 8.20
83425ExportYJan2020 4.16
93950ExportNJan2020 6.06
104198LocalNJan2020 14.91
114319LocalNJan2020 4.05
124319LocalNJan2020 4.12
134319LocalNJan2020 0.69
144319LocalNJan2020 2.03
154319LocalNJan2020 4.09
164319LocalNJan2020 1.35
174319LocalNJan2020 3.39
184319LocalNJan2020 0.35
194319LocalNJan2020 2.45
205810LocalNJan2020 2.99
215810LocalNJan2020 10.44
227110LocalNJan2020 1.89
233425ExportYJan2020 17.36
243950ExportNJan2020 0.86
253950ExportNJan2020 5.31
264222LocalNJan2020 4.06
274319LocalNJan2020 4.51
284319LocalNJan2020 2.30
297215LocalNJan2020 4.76
307215LocalNJan2020 1.70
318592LocalNJan2020 3.77
328592LocalNJan2020 3.38
338592LocalNJan2020 1.67
348592LocalNJan2020 6.01
354818ExportNJan2020 2.93
364818ExportNJan2020 20.76
374818ExportNJan2020 17.42
384198LocalNJan2020 4.93
394198LocalNJan2020 2.13
404160LocalNJan2020 0.40
414198LocalNJan2020 3.81
424319LocalNJan2020 4.41
434319LocalNJan2020 1.10
447215LocalNJan2020 7.26
456135LocalNJan2020 0.31
463425ExportYJan2020 16.24
473425ExportYJan2020 2.79
485585ExportYJan2020 229.52
491015ExportNJan2020 21.59
505585ExportYJan2020 227.15
515585ExportYJan2020 45.41
521015ExportNJan2020 20.88
533425ExportYJan2020 21.02
543425ExportYJan2020 9.18
553425ExportYJan2020 21.24
56180ExportNJan2020 13.96
57180ExportNJan2020 2.27
584222LocalNJan2020 41.35
594222LocalNJan2020 52.10
604222LocalNJan2020 27.47
614319LocalNJan2020 8.14
624319LocalNJan2020 11.73
634319LocalNJan2020 8.62
644319LocalNJan2020 0.35
654319LocalNJan2020 11.24
664319LocalNJan2020 5.63
674319LocalNJan2020 5.63
684319LocalNJan2020 2.82
694319LocalNJan2020 0.35
704319LocalNJan2020 8.42
715981ExportYJan2020 0.69
727215LocalNJan2020 1.51
737215LocalNJan2020 6.76
747215LocalNJan2020 30.59
757215LocalNJan2020 38.36
767215LocalNJan2020 3.94
777215LocalNJan2020 0.34
784071ExportNJan2020 22.14
794071ExportNJan2020 11.04
804319LocalNJan2020 5.05
814319LocalNJan2020 3.93
824319LocalNJan2020 3.53
836365ExportNJan2020 21.81
846365ExportNJan2020 22.08
856365ExportNJan2020 21.97
866365ExportNJan2020 22.11
876365ExportNJan2020 21.87
886365ExportNJan2020 21.63
896365ExportNJan2020 21.93
906365ExportNJan2020 22.02
916365ExportNJan2020 21.86
926365ExportNJan2020 21.87
936365ExportNJan2020 21.90
946365ExportNJan2020 22.00
956365ExportNJan2020 22.16
968450LocalNJan2020 10.46
97174ExportNJan2020 6.99
98834ExportNJan2020 0.65
99834ExportNJan2020 14.30
100938ExportNJan2020 15.53
101938ExportNJan2020 1.13
102938ExportNJan2020 2.73
1031482ExportNJan2020 5.35
1043242ExportNJan2020 22.34
1053425ExportYJan2020 4.98
1063425ExportYJan2020 4.52
1073425ExportYJan2020 7.04
1083425ExportYJan2020 8.99
1093425ExportYJan2020 17.18
1103425ExportYJan2020 6.95
1113425ExportYJan2020 3.92
1123712ExportNJan2020 3.17
1133712ExportNJan2020 29.67
1143719ExportNJan2020 21.56
1153950ExportNJan2020 6.51
1164060ExportNJan2020 8.26
1174060ExportNJan2020 22.02
1184071ExportNJan2020 10.95
1194304LocalNJan2020 3.04
1204304LocalNJan2020 0.39
1214319LocalNJan2020 2.30
1224319LocalNJan2020 5.14
1234319LocalNJan2020 1.96
1245680ExportYJan2020 22.12
1255981ExportYJan2020 4.99
1266135LocalNJan2020 0.11
1277215LocalNJan2020 1.98
1287215LocalNJan2020 4.30
1298592LocalNJan2020 5.21
1308592LocalNJan2020 2.23
1318592LocalNJan2020 2.50
1328592LocalNJan2020 0.42
1339115ExportNJan2020 4.81
1349115ExportNJan2020 0.67
1353712ExportNJan2020 19.87
136174ExportNJan2020 3.33
137180ExportNJan2020 5.78
138834ExportNJan2020 14.69
139834ExportNJan2020 22.59
140834ExportNJan2020 7.53
141938ExportNJan2020 4.87
142938ExportNJan2020 20.95
143938ExportNJan2020 13.00
1441380ExportNJan2020 77.98
1451482ExportNJan2020 21.50
1461482ExportNJan2020 21.48
1473425ExportYJan2020 5.73
1483425ExportYJan2020 21.24
1493425ExportYJan2020 1.68
1503425ExportYJan2020 10.55
1513425ExportYJan2020 22.45
1523425ExportYJan2020 2.48
1533425ExportYJan2020 3.84
1543425ExportYJan2020 7.10
1553425ExportYJan2020 50.22
1563425ExportYJan2020 17.44
1573712ExportNJan2020 150.51
1583712ExportNJan2020 79.16
1594060ExportNJan2020 13.38
1604060ExportNJan2020 5.92
1614319LocalNJan2020 12.40
1624814ExportNJan2020 20.44
1638227ExportNJan2020 10.37
1648227ExportNJan2020 35.79
1658227ExportNJan2020 13.26
1668592LocalNJan2020 7.81
1678592LocalNJan2020 1.72
168174ExportNJan2020 6.49
169834ExportNJan2020 85.64
170938ExportNJan2020 1.28
171938ExportNJan2020 6.17
1721065ExportNJan2020 21.58
1731130ExportNJan2020 21.43
1741380ExportNJan2020 29.53
1751482ExportNJan2020 3.83
1761482ExportNJan2020 21.27
1771482ExportNJan2020 21.41
1781482ExportNJan2020 6.96
1793425ExportYJan2020 9.34
1803425ExportYJan2020 4.93
1813425ExportYJan2020 4.89
1823425ExportYJan2020 6.89
1833425ExportYJan2020 1.25
1843425ExportYJan2020 6.07
1853712ExportNJan2020 71.04
1863712ExportNJan2020 50.76
1874060ExportNJan2020 10.33
1884060ExportNJan2020 15.50
1894222LocalNJan2020 2.65
1904434LocalNJan2020 3.04
1915680ExportYJan2020 10.98
1925981ExportYJan2020 5.61
1937655ExportNJan2020 22.31
1948227ExportNJan2020 33.37
1958227ExportNJan2020 8.05
1968227ExportNJan2020 29.42
1978592LocalNJan2020 7.58
198834ExportNJan2020 7.28
199834ExportNJan2020 21.66
200834ExportNJan2020 21.70
2011482ExportNJan2020 12.25
2023425ExportYJan2020 0.31
2033425ExportYJan2020 0.91
2043425ExportYJan2020 2.53
2053425ExportYJan2020 2.64
2063425ExportYJan2020 3.18
2073425ExportYJan2020 5.05
2083425ExportYJan2020 1.50
2093712ExportNJan2020 6.79
2103712ExportNJan2020 0.96
2114060ExportNJan2020 10.22
2124060ExportNJan2020 3.90
2134818ExportNJan2020 1.19
2145981ExportYJan2020 7.54
215174ExportNJan2020 3.64
216834ExportNJan2020 18.52
217834ExportNJan2020 4.63
218834ExportNJan2020 22.93
2191482ExportNJan2020 26.68
2201482ExportNJan2020 17.77
2211482ExportNJan2020 3.68
2223425ExportYJan2020 3.17
2233425ExportYJan2020 1.27
2243425ExportYJan2020 2.96
2253425ExportYJan2020 1.71
2263425ExportYJan2020 0.87
2273712ExportNJan2020 1.46
2283712ExportNJan2020 5.10
2293712ExportNJan2020 16.49
2304060ExportNJan2020 11.84
231834ExportNJan2020 22.70
2321482ExportNJan2020 8.18
2333425ExportYJan2020 6.37
2344060ExportNJan2020 5.96
2354222LocalNJan2020 1.71
2364222LocalNJan2020 4.12
2374319LocalNJan2020 4.01
2384319LocalNJan2020 3.22
2394319LocalNJan2020 4.46
2404319LocalNJan2020 4.05
2414319LocalNJan2020 1.63
2428592LocalNJan2020 3.60
2435680ExportYJan2020 4.67
2445680ExportYJan2020 6.07
2455585ExportYJan2020 170.74
2467100ExportNJan2020 0.04
2474071ExportNJan2020 22.62
2484071ExportNJan2020 22.62
2494071ExportNJan2020 (22.62)
2503425ExportYJan2020 8.52
2515981ExportYJan2020 21.29
2525981ExportYJan2020 21.70
2534319LocalNJan2020 2.76
2544319LocalNJan2020 4.65
2554319LocalNJan2020 6.89
2564198LocalNJan2020 4.00
2574198LocalNJan2020 2.82
2584319LocalNJan2020 5.28
2594319LocalNJan2020 3.33
2604319LocalNJan2020 7.52
2614319LocalNJan2020 5.84
2624319LocalNJan2020 11.41
2634319LocalNJan2020 0.32
2644319LocalNJan2020 1.09
2654319LocalNJan2020 1.35
2665981ExportYJan2020 2.63
2676135LocalNJan2020 0.13
2687215LocalNJan2020 7.86
2697215LocalNJan2020 1.29
2707215LocalNJan2020 0.86
2718592LocalNJan2020 3.37
2728592LocalNJan2020 2.37
2739115ExportNJan2020 4.59
2747215LocalNJan2020 7.43
2757215LocalNJan2020 25.37
2767215LocalNJan2020 2.52
2773425ExportYJan2020 9.61
2783425ExportYJan2020 16.11
2797215LocalNJan2020 18.43
2807215LocalNJan2020 0.40
2818592LocalNJan2020 3.82
2828592LocalNJan2020 1.17
2838592LocalNJan2020 2.28
2843425ExportYJan2020 3.93
2853425ExportYJan2020 5.39
2864221LocalNJan2020 3.48
2874221LocalNJan2020 3.51
2884221LocalNJan2020 4.09
2894221LocalNJan2020 1.77
2904221LocalNJan2020 8.77
2914221LocalNJan2020 2.47
2924221LocalNJan2020 1.85
2934221LocalNJan2020 6.51
2946595LocalNJan2020 2.34
2958065LocalNJan2020 0.22
2964149LocalNJan2020 5.70
2974149LocalNJan2020 0.46
2984149LocalNJan2020 5.11
2994149LocalNJan2020 0.80
3004140LocalNJan2020 3.63
3014140LocalNJan2020 17.06
3028455LocalNJan2020 2.50
3038455LocalNJan2020 0.50
3048455LocalNJan2020 3.00
3058455LocalNJan2020 0.50
3064255LocalNJan2020 2.00
3074255LocalNJan2020 2.00
3086135LocalNJan2020 0.09
3093176ExportYFeb2020 48.71
3103425ExportYFeb2020 5.11
3114198LocalNFeb2020 10.37
3124319LocalNFeb2020 0.04
313695ExportNFeb2020 20.77
3144198LocalNFeb2020 4.65
3154198LocalNFeb2020 2.32
3164198LocalNFeb2020 4.65
3177110LocalNFeb2020 3.17
3187110LocalNFeb2020 0.40
3194222LocalNFeb2020 12.34
3204222LocalNFeb2020 5.03
3214222LocalNFeb2020 5.95
3224222LocalNFeb2020 9.24
3234222LocalNFeb2020 4.26
3245360ExportNFeb2020 2.15
3255810LocalNFeb2020 6.46
3267215LocalNFeb2020 4.50
3277215LocalNFeb2020 8.88
3287215LocalNFeb2020 12.45
3298505ExportNFeb2020 4.67
3304222LocalNFeb2020 5.41
3313425ExportYFeb2020 1.00
3325680ExportYFeb2020 14.52
3335981ExportYFeb2020 0.03
3344198LocalNFeb2020 8.62
3354198LocalNFeb2020 17.07
3364198LocalNFeb2020 1.76
3374198LocalNFeb2020 8.81
3384198LocalNFeb2020 14.88
3393729ExportNFeb2020 7.24
3403729ExportNFeb2020 8.89
3414160LocalNFeb2020 0.79
3424198LocalNFeb2020 2.39
3434198LocalNFeb2020 2.40
3444222LocalNFeb2020 10.61
3457110LocalNFeb2020 6.31
3467110LocalNFeb2020 8.24
3478592LocalNFeb2020 3.42
3488592LocalNFeb2020 1.30
3498592LocalNFeb2020 3.83
3503425ExportYFeb2020 1.24
3513425ExportYFeb2020 7.79
3524160LocalNFeb2020 1.16
3535981ExportYFeb2020 6.65
3545585ExportYFeb2020 23.20
3555585ExportYFeb2020 23.07
3563425ExportYFeb2020 21.66
3575585ExportYFeb2020 225.81
3585585ExportYFeb2020 225.81
3595585ExportYFeb2020 (225.81)
3601015ExportNFeb2020 21.70
3611015ExportNFeb2020 21.39
3623425ExportYFeb2020 22.68
3633425ExportYFeb2020 19.18
3643425ExportYFeb2020 19.29
3653425ExportYFeb2020 53.58
3665585ExportYFeb2020 157.42
3675585ExportYFeb2020 112.19
3681015ExportNFeb2020 21.01
3693425ExportYFeb2020 20.95
3703425ExportYFeb2020 22.71
3713425ExportYFeb2020 43.42
3723425ExportYFeb2020 11.54
2020



sheet2

MMM.xlsx
ABCDEFGHIJ
1Sales Volume Analysis
2Mar
3Volume by Mt
4
5Last YearActualBudgetActual vs Last YearActual vs Budget
6Market2019%2020%2020%
7Export 12,972 79%
8Local 3,487 21%
9 Total 16,459
YTD
Cell Formulas
RangeFormula
G7:G8,E7:E8,C7:C8C7=IFERROR(B7/B$9,"")
D7:D8D7=SUMIFS('2020'!$F$4:$F$6000,'2020'!$B$4:$B$6000,$A7)
D9D9=SUM(D7:D8)
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
You just add another criteria to your Sumifs...

Book1
ABCDEF
1Sales Volume Analysis
2Jan
3Volume by Mt
4
5Last YearActualBudget
6Market2019%2020%2020
7Export 3188.8030.819554
8Local 702.09510.180446
9 Total3890.898
ytd
Cell Formulas
RangeFormula
C7:C8,E7:E8C7=IFERROR(B7/B$9,"")
D7:D8D7=SUMIFS('2020'!$F$4:$F$6000,'2020'!$B$4:$B$6000,$A7,'2020'!$D$4:$D$6000,$A$2)
D9D9=SUM(D7:D8)
 
Upvote 0
hi, mark ,your formula works but i'm sorry say this i need adjusting i would not sum every month , but sum the firs three months like this when i choose mar in a2 sum values in month 1,2,3
 
Upvote 0
I am not sure that this is possible without a lookup table as A2 and column D in the other sheet are just text and not actual months.
Maybe someone else will have an idea.
 
Upvote 0
hi, mark i found your formula not work well in the beginning sum the first three months then sum each month alone !!!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

Deepakchoudhary
  • Question Question
Database & Bar Chart preparation for comparison of 3 parameters, 5 items on Dynamic selection of OUTLETS
Replies
0
Views
103
Deepakchoudhary
Deepakchoudhary
1
  • Question Question
Calculate on every third Friday of the month and show results between positive & negative weeks
2
Replies
14
Views
957
15minoffame
1

Forum statistics

Threads
1,223,912
Messages
6,175,340
Members
452,638
Latest member
Oluwabukunmi

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