Hello,
I need to consolidate monthly targets data for salespersons by summing 2, 3 or more months together.
Details: I have a table with salespersons and their monthly targets, January through December. Sometimes I need to sum data from specific months, for example January+February+March or July+August etc in a new sheet, using a form.
I am too stupid to do this and I will really appreciate any help.
Thank you!
I need to consolidate monthly targets data for salespersons by summing 2, 3 or more months together.
Details: I have a table with salespersons and their monthly targets, January through December. Sometimes I need to sum data from specific months, for example January+February+March or July+August etc in a new sheet, using a form.
I am too stupid to do this and I will really appreciate any help.
Thank you!
Dummy.xlsm | |||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | AN | AO | |||
1 | Region | Sales person | January | February | March | April | May | June | July | August | September | October | November | December | TOTAL 2024 | ||||||||||||||||||||||||||||
2 | Car A | Car B | Car C | Car A | Car B | Car C | Car A | Car B | Car C | Car A | Car B | Car C | Car A | Car B | Car C | Car A | Car B | Car C | Car A | Car B | Car C | Car A | Car B | Car C | Car A | Car B | Car C | Car A | Car B | Car C | Car A | Car B | Car C | Car A | Car B | Car C | Car A | Car B | Car C | ||||
3 | COMPANY TOTAL | 103 | 100 | 102 | 104 | 99 | 103 | 107 | 101 | 103 | 104 | 104 | 100 | 104 | 101 | 102 | 105 | 100 | 103 | 105 | 103 | 103 | 102 | 107 | 102 | 103 | 102 | 101 | 104 | 100 | 101 | 103 | 103 | 101 | 106 | 100 | 103 | 1250 | 1220 | 1224 | |||
4 | Region 1 | John | 10 | 12 | 11 | 9 | 10 | 11 | 12 | 12 | 13 | 11 | 14 | 11 | 9 | 11 | 10 | 10 | 10 | 11 | 9 | 12 | 11 | 10 | 12 | 11 | 10 | 12 | 11 | 10 | 12 | 11 | 10 | 12 | 11 | 10 | 12 | 11 | 120 | 141 | 133 | ||
5 | Region 1 | Mary | 12 | 10 | 9 | 12 | 10 | 9 | 12 | 10 | 9 | 12 | 10 | 9 | 12 | 10 | 9 | 12 | 10 | 9 | 12 | 10 | 9 | 12 | 10 | 9 | 12 | 10 | 9 | 12 | 10 | 9 | 12 | 10 | 9 | 12 | 10 | 9 | 144 | 120 | 108 | ||
6 | Region 1 | Sam | 8 | 7 | 8 | 8 | 7 | 8 | 8 | 7 | 8 | 8 | 7 | 8 | 8 | 7 | 8 | 8 | 7 | 8 | 8 | 7 | 8 | 8 | 7 | 8 | 8 | 7 | 8 | 8 | 7 | 8 | 8 | 7 | 8 | 8 | 7 | 8 | 96 | 84 | 96 | ||
7 | Region 1 | Willie | 14 | 12 | 11 | 14 | 12 | 11 | 14 | 12 | 11 | 14 | 12 | 11 | 14 | 12 | 11 | 14 | 12 | 11 | 14 | 12 | 11 | 14 | 12 | 11 | 14 | 12 | 11 | 14 | 12 | 11 | 14 | 12 | 11 | 14 | 12 | 11 | 168 | 144 | 132 | ||
8 | REGION 1 TOTAL | 44 | 41 | 39 | 43 | 39 | 39 | 46 | 41 | 41 | 45 | 43 | 39 | 43 | 40 | 38 | 44 | 39 | 39 | 43 | 41 | 39 | 44 | 41 | 39 | 44 | 41 | 39 | 44 | 41 | 39 | 44 | 41 | 39 | 44 | 41 | 39 | 528 | 489 | 469 | |||
9 | Region 2 | Kevin | 9 | 9 | 8 | 10 | 9 | 8 | 9 | 7 | 8 | 9 | 10 | 8 | 10 | 9 | 8 | 11 | 9 | 8 | 12 | 9 | 8 | 10 | 9 | 8 | 11 | 9 | 8 | 10 | 9 | 8 | 9 | 10 | 8 | 11 | 9 | 8 | 121 | 108 | 96 | ||
10 | Region 2 | Sandra | 10 | 9 | 11 | 11 | 9 | 11 | 12 | 9 | 10 | 10 | 10 | 9 | 10 | 9 | 12 | 10 | 10 | 11 | 10 | 11 | 11 | 10 | 12 | 11 | 10 | 10 | 11 | 10 | 9 | 10 | 10 | 9 | 9 | 10 | 9 | 11 | 123 | 116 | 127 | ||
11 | Region 2 | Michael | 8 | 10 | 12 | 8 | 11 | 12 | 8 | 12 | 12 | 9 | 10 | 12 | 9 | 10 | 12 | 8 | 11 | 12 | 8 | 10 | 13 | 8 | 14 | 12 | 8 | 11 | 12 | 9 | 10 | 12 | 8 | 10 | 13 | 9 | 10 | 12 | 100 | 129 | 146 | ||
12 | REGION 2 TOTAL | 27 | 28 | 31 | 29 | 29 | 31 | 29 | 28 | 30 | 28 | 30 | 29 | 29 | 28 | 32 | 29 | 30 | 31 | 30 | 30 | 32 | 28 | 35 | 31 | 29 | 30 | 31 | 29 | 28 | 30 | 27 | 29 | 30 | 30 | 28 | 31 | 344 | 353 | 369 | |||
13 | Region 3 | Dan | 11 | 11 | 12 | 11 | 11 | 12 | 11 | 11 | 12 | 11 | 11 | 12 | 11 | 11 | 12 | 11 | 11 | 12 | 11 | 11 | 12 | 11 | 11 | 12 | 11 | 11 | 12 | 11 | 11 | 12 | 11 | 11 | 12 | 11 | 11 | 12 | 132 | 132 | 144 | ||
14 | Region 3 | Olivia | 9 | 10 | 9 | 9 | 10 | 9 | 9 | 10 | 9 | 9 | 10 | 9 | 9 | 10 | 9 | 9 | 10 | 9 | 9 | 10 | 9 | 9 | 10 | 9 | 9 | 10 | 9 | 9 | 10 | 9 | 9 | 10 | 9 | 9 | 10 | 9 | 108 | 120 | 108 | ||
15 | Region 3 | Ava | 12 | 10 | 11 | 12 | 10 | 12 | 12 | 11 | 11 | 11 | 10 | 11 | 12 | 12 | 11 | 12 | 10 | 12 | 12 | 11 | 11 | 10 | 10 | 11 | 10 | 10 | 10 | 11 | 10 | 11 | 12 | 12 | 11 | 12 | 10 | 12 | 138 | 126 | 134 | ||
16 | REGION 3 TOTAL | 32 | 31 | 32 | 32 | 31 | 33 | 32 | 32 | 32 | 31 | 31 | 32 | 32 | 33 | 32 | 32 | 31 | 33 | 32 | 32 | 32 | 30 | 31 | 32 | 30 | 31 | 31 | 31 | 31 | 32 | 32 | 33 | 32 | 32 | 31 | 33 | 378 | 378 | 386 | |||
17 | |||||||||||||||||||||||||||||||||||||||||||
18 | |||||||||||||||||||||||||||||||||||||||||||
19 | I need a macro that can sum monthly data by my choice (using a form, like in the picture) | ||||||||||||||||||||||||||||||||||||||||||
20 | and put the results in a new sheet (like sheet "Result") | ||||||||||||||||||||||||||||||||||||||||||
21 | Eg: | January+February | |||||||||||||||||||||||||||||||||||||||||
22 | or | January+February+March | |||||||||||||||||||||||||||||||||||||||||
23 | or | April+May+June (Q2) | |||||||||||||||||||||||||||||||||||||||||
24 | etc | ||||||||||||||||||||||||||||||||||||||||||
Data |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C3:AO3 | C3 | =C8+C12+C16 |
AM4:AO7,AM9:AO11,AM13:AO15 | AM4 | =C4+F4+I4+L4+O4+R4+U4+X4+AA4+AD4+AG4+AJ4 |
C8:AO8 | AM8 | =SUM(AM4:AM7) |
C16:AO16,C12:AO12 | AM12 | =SUM(AM9:AM11) |
Dummy.xlsm | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | |||
1 | Region | Sales person | January+February+March | ||||||||||
2 | Car A | Car B | Car C | ||||||||||
3 | COMPANY TOTAL | 314 | 300 | 308 | |||||||||
4 | Region 1 | John | 31 | 34 | 35 | ||||||||
5 | Region 1 | Mary | 36 | 30 | 27 | ||||||||
6 | Region 1 | Sam | 24 | 21 | 24 | ||||||||
7 | Region 1 | Willie | 42 | 36 | 33 | ||||||||
8 | REGION 1 TOTAL | 133 | 121 | 119 | |||||||||
9 | Region 2 | Kevin | 28 | 25 | 24 | ||||||||
10 | Region 2 | Sandra | 33 | 27 | 32 | ||||||||
11 | Region 2 | Michael | 24 | 33 | 36 | ||||||||
12 | REGION 2 TOTAL | 85 | 85 | 92 | |||||||||
13 | Region 2 | Dan | 33 | 33 | 36 | ||||||||
14 | Region 2 | Olivia | 27 | 30 | 27 | ||||||||
15 | Region 2 | Ava | 36 | 31 | 34 | ||||||||
16 | REGION 3 TOTAL | 96 | 94 | 97 | |||||||||
Result |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C3:E3 | C3 | =C8+C12+C16 |
C8:E8 | C8 | =SUM(C4:C7) |
C12:E12,C16:E16 | C12 | =SUM(C9:C11) |