Looking Urgent Help : SUMIFS functions with HLOOKUP/INDEX & MATCH functions

M

Morshed_Dhaka

New Member
Joined
Dec 16, 2016
Messages
42
Hi,

For last couple of days, I am trying to solve this problem but didn't find out proper solution of this problem. could anybody please suggest me in which way I can resolve it ? here is my problem :

in sheet 1, I have area, territory, brand wise daily sales data for the next year. in the 2nd sheet, I am looking for specific area, brand and territory wise data wise sales data which means I can easily sum up area, brand & territory wise data from sheet 1 by using sumifs function. but the problem is date. I am looking for the exact date data. suppose, I can use sumifs function to collect area wise, territory wise brand wise 1st Jan 2017 data. but as the date was in the row, either I have to use hlookup function or index function. I am trying but it doesn't work. really appreciate if anybody helps.

thanks.

[TABLE="width: 976"]
<tbody>[TR]
[TD]Area[/TD]
[TD]Territory[/TD]
[TD]Brand[/TD]
[TD]1-Jan-17[/TD]
[TD]2-Jan-17[/TD]
[TD]3-Jan-17[/TD]
[TD]4-Jan-17[/TD]
[TD]5-Jan-17[/TD]
[TD]6-Jan-17[/TD]
[TD]7-Jan-17[/TD]
[TD]8-Jan-17[/TD]
[TD]9-Jan-17[/TD]
[TD]10-Jan-17[/TD]
[/TR]
[TR]
[TD]AREA 1[/TD]
[TD]TERRITORY 1[/TD]
[TD]BRAND A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AREA 1[/TD]
[TD]TERRITORY 1[/TD]
[TD]BRAND B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AREA 1[/TD]
[TD]TERRITORY 1[/TD]
[TD]BRAND C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AREA 1[/TD]
[TD]TERRITORY 1[/TD]
[TD]BRAND D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AREA 2[/TD]
[TD]TERRITORY 1[/TD]
[TD]BRAND A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AREA 2[/TD]
[TD]TERRITORY 1[/TD]
[TD]BRAND B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AREA 2[/TD]
[TD]TERRITORY 1[/TD]
[TD]BRAND C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AREA 2[/TD]
[TD]TERRITORY 1[/TD]
[TD]BRAND D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col span="8"><col></colgroup>[/TABLE]


[TABLE="width: 733"]
<colgroup><col width="103" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3766;"> <col width="102" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3730;"> <col width="122" style="width: 92pt; mso-width-source: userset; mso-width-alt: 4461;"> <col width="67" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2450;"> <col width="64" style="width: 48pt;" span="8"> <col width="70" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2560;"> <tbody>[TR]
[TD="class: xl67, width: 103, bgcolor: #C00000"]Area[/TD]
[TD="class: xl67, width: 102, bgcolor: #C00000"]Territory[/TD]
[TD="class: xl67, width: 122, bgcolor: #C00000"]Brand[/TD]
[TD="class: xl65, width: 67, bgcolor: transparent"]1-Jan-17[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]2-Jan-17[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]3-Jan-17[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]4-Jan-17[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]5-Jan-17[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]6-Jan-17[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]7-Jan-17[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]8-Jan-17[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]9-Jan-17[/TD]
[TD="class: xl65, width: 70, bgcolor: transparent"]10-Jan-17[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]AREA 1[/TD]
[TD="class: xl68, bgcolor: transparent"]TERRITORY 1[/TD]
[TD="class: xl68, bgcolor: transparent"]BRAND A[/TD]
[TD="class: xl69, bgcolor: transparent"] 1,160 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,290 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,420 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,550 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,680 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,810 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,940 [/TD]
[TD="class: xl70, bgcolor: transparent"] 2,070 [/TD]
[TD="class: xl70, bgcolor: transparent"] 2,200 [/TD]
[TD="class: xl70, bgcolor: transparent"] 2,330 [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]AREA 1[/TD]
[TD="class: xl68, bgcolor: transparent"]TERRITORY 1[/TD]
[TD="class: xl68, bgcolor: transparent"]BRAND A[/TD]
[TD="class: xl69, bgcolor: transparent"] 2,500 [/TD]
[TD="class: xl70, bgcolor: transparent"] 2,630 [/TD]
[TD="class: xl70, bgcolor: transparent"] 2,760 [/TD]
[TD="class: xl70, bgcolor: transparent"] 2,890 [/TD]
[TD="class: xl70, bgcolor: transparent"] 3,020 [/TD]
[TD="class: xl70, bgcolor: transparent"] 3,150 [/TD]
[TD="class: xl70, bgcolor: transparent"] 3,280 [/TD]
[TD="class: xl70, bgcolor: transparent"] 3,410 [/TD]
[TD="class: xl70, bgcolor: transparent"] 3,540 [/TD]
[TD="class: xl70, bgcolor: transparent"] 3,670 [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]AREA 1[/TD]
[TD="class: xl68, bgcolor: transparent"]TERRITORY 1[/TD]
[TD="class: xl68, bgcolor: transparent"]BRAND B[/TD]
[TD="class: xl69, bgcolor: transparent"] 1,180 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,310 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,440 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,570 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,700 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,830 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,960 [/TD]
[TD="class: xl70, bgcolor: transparent"] 2,090 [/TD]
[TD="class: xl70, bgcolor: transparent"] 2,220 [/TD]
[TD="class: xl70, bgcolor: transparent"] 2,350 [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]AREA 1[/TD]
[TD="class: xl68, bgcolor: transparent"]TERRITORY 1[/TD]
[TD="class: xl68, bgcolor: transparent"]BRAND B[/TD]
[TD="class: xl69, bgcolor: transparent"] 2,500 [/TD]
[TD="class: xl70, bgcolor: transparent"] 2,630 [/TD]
[TD="class: xl70, bgcolor: transparent"] 2,760 [/TD]
[TD="class: xl70, bgcolor: transparent"] 2,890 [/TD]
[TD="class: xl70, bgcolor: transparent"] 3,020 [/TD]
[TD="class: xl70, bgcolor: transparent"] 3,150 [/TD]
[TD="class: xl70, bgcolor: transparent"] 3,280 [/TD]
[TD="class: xl70, bgcolor: transparent"] 3,410 [/TD]
[TD="class: xl70, bgcolor: transparent"] 3,540 [/TD]
[TD="class: xl70, bgcolor: transparent"] 3,670 [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]AREA 1[/TD]
[TD="class: xl68, bgcolor: transparent"]TERRITORY 1[/TD]
[TD="class: xl68, bgcolor: transparent"]BRAND C[/TD]
[TD="class: xl69, bgcolor: transparent"] 1,180 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,310 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,440 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,570 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,700 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,830 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,960 [/TD]
[TD="class: xl70, bgcolor: transparent"] 2,090 [/TD]
[TD="class: xl70, bgcolor: transparent"] 2,220 [/TD]
[TD="class: xl70, bgcolor: transparent"] 2,350 [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]AREA 1[/TD]
[TD="class: xl68, bgcolor: transparent"]TERRITORY 1[/TD]
[TD="class: xl68, bgcolor: transparent"]BRAND C[/TD]
[TD="class: xl69, bgcolor: transparent"] - [/TD]
[TD="class: xl70, bgcolor: transparent"] 130 [/TD]
[TD="class: xl70, bgcolor: transparent"] 260 [/TD]
[TD="class: xl70, bgcolor: transparent"] 390 [/TD]
[TD="class: xl70, bgcolor: transparent"] 520 [/TD]
[TD="class: xl70, bgcolor: transparent"] 650 [/TD]
[TD="class: xl70, bgcolor: transparent"] 780 [/TD]
[TD="class: xl70, bgcolor: transparent"] 910 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,040 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,170 [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]AREA 1[/TD]
[TD="class: xl68, bgcolor: transparent"]TERRITORY 1[/TD]
[TD="class: xl68, bgcolor: transparent"]BRAND D[/TD]
[TD="class: xl69, bgcolor: transparent"] - [/TD]
[TD="class: xl70, bgcolor: transparent"] 130 [/TD]
[TD="class: xl70, bgcolor: transparent"] 260 [/TD]
[TD="class: xl70, bgcolor: transparent"] 390 [/TD]
[TD="class: xl70, bgcolor: transparent"] 520 [/TD]
[TD="class: xl70, bgcolor: transparent"] 650 [/TD]
[TD="class: xl70, bgcolor: transparent"] 780 [/TD]
[TD="class: xl70, bgcolor: transparent"] 910 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,040 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,170 [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]AREA 1[/TD]
[TD="class: xl68, bgcolor: transparent"]TERRITORY 1[/TD]
[TD="class: xl68, bgcolor: transparent"]BRAND D[/TD]
[TD="class: xl69, bgcolor: transparent"] 400 [/TD]
[TD="class: xl70, bgcolor: transparent"] 530 [/TD]
[TD="class: xl70, bgcolor: transparent"] 660 [/TD]
[TD="class: xl70, bgcolor: transparent"] 790 [/TD]
[TD="class: xl70, bgcolor: transparent"] 920 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,050 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,180 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,310 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,440 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,570 [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]AREA 1[/TD]
[TD="class: xl68, bgcolor: transparent"]TERRITORY 1[/TD]
[TD="class: xl68, bgcolor: transparent"]BRAND A[/TD]
[TD="class: xl69, bgcolor: transparent"] 80 [/TD]
[TD="class: xl70, bgcolor: transparent"] 210 [/TD]
[TD="class: xl70, bgcolor: transparent"] 340 [/TD]
[TD="class: xl70, bgcolor: transparent"] 470 [/TD]
[TD="class: xl70, bgcolor: transparent"] 600 [/TD]
[TD="class: xl70, bgcolor: transparent"] 730 [/TD]
[TD="class: xl70, bgcolor: transparent"] 860 [/TD]
[TD="class: xl70, bgcolor: transparent"] 990 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,120 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,250 [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]AREA 1[/TD]
[TD="class: xl68, bgcolor: transparent"]TERRITORY 1[/TD]
[TD="class: xl68, bgcolor: transparent"]BRAND A[/TD]
[TD="class: xl69, bgcolor: transparent"] - [/TD]
[TD="class: xl70, bgcolor: transparent"] 130 [/TD]
[TD="class: xl70, bgcolor: transparent"] 260 [/TD]
[TD="class: xl70, bgcolor: transparent"] 390 [/TD]
[TD="class: xl70, bgcolor: transparent"] 520 [/TD]
[TD="class: xl70, bgcolor: transparent"] 650 [/TD]
[TD="class: xl70, bgcolor: transparent"] 780 [/TD]
[TD="class: xl70, bgcolor: transparent"] 910 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,040 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,170 [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]AREA 1[/TD]
[TD="class: xl68, bgcolor: transparent"]TERRITORY 1[/TD]
[TD="class: xl68, bgcolor: transparent"]BRAND B[/TD]
[TD="class: xl69, bgcolor: transparent"] 10 [/TD]
[TD="class: xl70, bgcolor: transparent"] 140 [/TD]
[TD="class: xl70, bgcolor: transparent"] 270 [/TD]
[TD="class: xl70, bgcolor: transparent"] 400 [/TD]
[TD="class: xl70, bgcolor: transparent"] 530 [/TD]
[TD="class: xl70, bgcolor: transparent"] 660 [/TD]
[TD="class: xl70, bgcolor: transparent"] 790 [/TD]
[TD="class: xl70, bgcolor: transparent"] 920 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,050 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,180 [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]AREA 1[/TD]
[TD="class: xl68, bgcolor: transparent"]TERRITORY 1[/TD]
[TD="class: xl68, bgcolor: transparent"]BRAND B[/TD]
[TD="class: xl69, bgcolor: transparent"] 620 [/TD]
[TD="class: xl70, bgcolor: transparent"] 750 [/TD]
[TD="class: xl70, bgcolor: transparent"] 880 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,010 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,140 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,270 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,400 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,530 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,660 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,790 [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]AREA 1[/TD]
[TD="class: xl68, bgcolor: transparent"]TERRITORY 1[/TD]
[TD="class: xl68, bgcolor: transparent"]BRAND C[/TD]
[TD="class: xl69, bgcolor: transparent"] 630 [/TD]
[TD="class: xl70, bgcolor: transparent"] 760 [/TD]
[TD="class: xl70, bgcolor: transparent"] 890 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,020 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,150 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,280 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,410 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,540 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,670 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,800 [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]AREA 1[/TD]
[TD="class: xl68, bgcolor: transparent"]TERRITORY 1[/TD]
[TD="class: xl68, bgcolor: transparent"]BRAND C[/TD]
[TD="class: xl69, bgcolor: transparent"] 120 [/TD]
[TD="class: xl70, bgcolor: transparent"] 250 [/TD]
[TD="class: xl70, bgcolor: transparent"] 380 [/TD]
[TD="class: xl70, bgcolor: transparent"] 510 [/TD]
[TD="class: xl70, bgcolor: transparent"] 640 [/TD]
[TD="class: xl70, bgcolor: transparent"] 770 [/TD]
[TD="class: xl70, bgcolor: transparent"] 900 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,030 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,160 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,290 [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]AREA 1[/TD]
[TD="class: xl68, bgcolor: transparent"]TERRITORY 1[/TD]
[TD="class: xl68, bgcolor: transparent"]BRAND D[/TD]
[TD="class: xl69, bgcolor: transparent"] 100 [/TD]
[TD="class: xl70, bgcolor: transparent"] 230 [/TD]
[TD="class: xl70, bgcolor: transparent"] 360 [/TD]
[TD="class: xl70, bgcolor: transparent"] 490 [/TD]
[TD="class: xl70, bgcolor: transparent"] 620 [/TD]
[TD="class: xl70, bgcolor: transparent"] 750 [/TD]
[TD="class: xl70, bgcolor: transparent"] 880 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,010 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,140 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,270 [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]AREA 1[/TD]
[TD="class: xl68, bgcolor: transparent"]TERRITORY 1[/TD]
[TD="class: xl68, bgcolor: transparent"]BRAND D[/TD]
[TD="class: xl69, bgcolor: transparent"] - [/TD]
[TD="class: xl70, bgcolor: transparent"] 130 [/TD]
[TD="class: xl70, bgcolor: transparent"] 260 [/TD]
[TD="class: xl70, bgcolor: transparent"] 390 [/TD]
[TD="class: xl70, bgcolor: transparent"] 520 [/TD]
[TD="class: xl70, bgcolor: transparent"] 650 [/TD]
[TD="class: xl70, bgcolor: transparent"] 780 [/TD]
[TD="class: xl70, bgcolor: transparent"] 910 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,040 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,170 [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]AREA 2[/TD]
[TD="class: xl68, bgcolor: transparent"]TERRITORY 2[/TD]
[TD="class: xl68, bgcolor: transparent"]BRAND A[/TD]
[TD="class: xl69, bgcolor: transparent"] 910 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,040 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,170 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,300 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,430 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,560 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,690 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,820 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,950 [/TD]
[TD="class: xl70, bgcolor: transparent"] 2,080 [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]AREA 2[/TD]
[TD="class: xl68, bgcolor: transparent"]TERRITORY 2[/TD]
[TD="class: xl68, bgcolor: transparent"]BRAND A[/TD]
[TD="class: xl69, bgcolor: transparent"] 2,160 [/TD]
[TD="class: xl70, bgcolor: transparent"] 2,290 [/TD]
[TD="class: xl70, bgcolor: transparent"] 2,420 [/TD]
[TD="class: xl70, bgcolor: transparent"] 2,550 [/TD]
[TD="class: xl70, bgcolor: transparent"] 2,680 [/TD]
[TD="class: xl70, bgcolor: transparent"] 2,810 [/TD]
[TD="class: xl70, bgcolor: transparent"] 2,940 [/TD]
[TD="class: xl70, bgcolor: transparent"] 3,070 [/TD]
[TD="class: xl70, bgcolor: transparent"] 3,200 [/TD]
[TD="class: xl70, bgcolor: transparent"] 3,330 [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]AREA 2[/TD]
[TD="class: xl68, bgcolor: transparent"]TERRITORY 2[/TD]
[TD="class: xl68, bgcolor: transparent"]BRAND B[/TD]
[TD="class: xl69, bgcolor: transparent"] 900 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,030 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,160 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,290 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,420 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,550 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,680 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,810 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,940 [/TD]
[TD="class: xl70, bgcolor: transparent"] 2,070 [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]AREA 2[/TD]
[TD="class: xl68, bgcolor: transparent"]TERRITORY 2[/TD]
[TD="class: xl68, bgcolor: transparent"]BRAND B[/TD]
[TD="class: xl69, bgcolor: transparent"] 8,040 [/TD]
[TD="class: xl70, bgcolor: transparent"] 8,170 [/TD]
[TD="class: xl70, bgcolor: transparent"] 8,300 [/TD]
[TD="class: xl70, bgcolor: transparent"] 8,430 [/TD]
[TD="class: xl70, bgcolor: transparent"] 8,560 [/TD]
[TD="class: xl70, bgcolor: transparent"] 8,690 [/TD]
[TD="class: xl70, bgcolor: transparent"] 8,820 [/TD]
[TD="class: xl70, bgcolor: transparent"] 8,950 [/TD]
[TD="class: xl70, bgcolor: transparent"] 9,080 [/TD]
[TD="class: xl70, bgcolor: transparent"] 9,210 [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]AREA 2[/TD]
[TD="class: xl68, bgcolor: transparent"]TERRITORY 2[/TD]
[TD="class: xl68, bgcolor: transparent"]BRAND C[/TD]
[TD="class: xl69, bgcolor: transparent"] 1,310 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,440 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,570 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,700 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,830 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,960 [/TD]
[TD="class: xl70, bgcolor: transparent"] 2,090 [/TD]
[TD="class: xl70, bgcolor: transparent"] 2,220 [/TD]
[TD="class: xl70, bgcolor: transparent"] 2,350 [/TD]
[TD="class: xl70, bgcolor: transparent"] 2,480 [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]AREA 2[/TD]
[TD="class: xl68, bgcolor: transparent"]TERRITORY 2[/TD]
[TD="class: xl68, bgcolor: transparent"]BRAND C[/TD]
[TD="class: xl69, bgcolor: transparent"] 6,560 [/TD]
[TD="class: xl70, bgcolor: transparent"] 6,690 [/TD]
[TD="class: xl70, bgcolor: transparent"] 6,820 [/TD]
[TD="class: xl70, bgcolor: transparent"] 6,950 [/TD]
[TD="class: xl70, bgcolor: transparent"] 7,080 [/TD]
[TD="class: xl70, bgcolor: transparent"] 7,210 [/TD]
[TD="class: xl70, bgcolor: transparent"] 7,340 [/TD]
[TD="class: xl70, bgcolor: transparent"] 7,470 [/TD]
[TD="class: xl70, bgcolor: transparent"] 7,600 [/TD]
[TD="class: xl70, bgcolor: transparent"] 7,730 [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]AREA 2[/TD]
[TD="class: xl68, bgcolor: transparent"]TERRITORY 2[/TD]
[TD="class: xl68, bgcolor: transparent"]BRAND D[/TD]
[TD="class: xl69, bgcolor: transparent"] 420 [/TD]
[TD="class: xl70, bgcolor: transparent"] 550 [/TD]
[TD="class: xl70, bgcolor: transparent"] 680 [/TD]
[TD="class: xl70, bgcolor: transparent"] 810 [/TD]
[TD="class: xl70, bgcolor: transparent"] 940 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,070 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,200 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,330 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,460 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,590 [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]AREA 2[/TD]
[TD="class: xl68, bgcolor: transparent"]TERRITORY 2[/TD]
[TD="class: xl68, bgcolor: transparent"]BRAND D[/TD]
[TD="class: xl69, bgcolor: transparent"] 460 [/TD]
[TD="class: xl70, bgcolor: transparent"] 590 [/TD]
[TD="class: xl70, bgcolor: transparent"] 720 [/TD]
[TD="class: xl70, bgcolor: transparent"] 850 [/TD]
[TD="class: xl70, bgcolor: transparent"] 980 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,110 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,240 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,370 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,500 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,630 [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]AREA 2[/TD]
[TD="class: xl68, bgcolor: transparent"]TERRITORY 3[/TD]
[TD="class: xl71, bgcolor: transparent"]BRAND A[/TD]
[TD="class: xl69, bgcolor: transparent"] - [/TD]
[TD="class: xl70, bgcolor: transparent"] 130 [/TD]
[TD="class: xl70, bgcolor: transparent"] 260 [/TD]
[TD="class: xl70, bgcolor: transparent"] 390 [/TD]
[TD="class: xl70, bgcolor: transparent"] 520 [/TD]
[TD="class: xl70, bgcolor: transparent"] 650 [/TD]
[TD="class: xl70, bgcolor: transparent"] 780 [/TD]
[TD="class: xl70, bgcolor: transparent"] 910 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,040 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,170 [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]AREA 2[/TD]
[TD="class: xl68, bgcolor: transparent"]TERRITORY 3[/TD]
[TD="class: xl71, bgcolor: transparent"]BRAND A[/TD]
[TD="class: xl69, bgcolor: transparent"] 200 [/TD]
[TD="class: xl70, bgcolor: transparent"] 330 [/TD]
[TD="class: xl70, bgcolor: transparent"] 460 [/TD]
[TD="class: xl70, bgcolor: transparent"] 590 [/TD]
[TD="class: xl70, bgcolor: transparent"] 720 [/TD]
[TD="class: xl70, bgcolor: transparent"] 850 [/TD]
[TD="class: xl70, bgcolor: transparent"] 980 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,110 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,240 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,370 [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]AREA 2[/TD]
[TD="class: xl68, bgcolor: transparent"]TERRITORY 3[/TD]
[TD="class: xl71, bgcolor: transparent"]BRAND B[/TD]
[TD="class: xl69, bgcolor: transparent"] - [/TD]
[TD="class: xl70, bgcolor: transparent"] 130 [/TD]
[TD="class: xl70, bgcolor: transparent"] 260 [/TD]
[TD="class: xl70, bgcolor: transparent"] 390 [/TD]
[TD="class: xl70, bgcolor: transparent"] 520 [/TD]
[TD="class: xl70, bgcolor: transparent"] 650 [/TD]
[TD="class: xl70, bgcolor: transparent"] 780 [/TD]
[TD="class: xl70, bgcolor: transparent"] 910 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,040 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,170 [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]AREA 2[/TD]
[TD="class: xl68, bgcolor: transparent"]TERRITORY 3[/TD]
[TD="class: xl71, bgcolor: transparent"]BRAND B[/TD]
[TD="class: xl69, bgcolor: transparent"] 1,200 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,330 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,460 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,590 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,720 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,850 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,980 [/TD]
[TD="class: xl70, bgcolor: transparent"] 2,110 [/TD]
[TD="class: xl70, bgcolor: transparent"] 2,240 [/TD]
[TD="class: xl70, bgcolor: transparent"] 2,370 [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]AREA 2[/TD]
[TD="class: xl68, bgcolor: transparent"]TERRITORY 3[/TD]
[TD="class: xl71, bgcolor: transparent"]BRAND C[/TD]
[TD="class: xl69, bgcolor: transparent"] - [/TD]
[TD="class: xl70, bgcolor: transparent"] 130 [/TD]
[TD="class: xl70, bgcolor: transparent"] 260 [/TD]
[TD="class: xl70, bgcolor: transparent"] 390 [/TD]
[TD="class: xl70, bgcolor: transparent"] 520 [/TD]
[TD="class: xl70, bgcolor: transparent"] 650 [/TD]
[TD="class: xl70, bgcolor: transparent"] 780 [/TD]
[TD="class: xl70, bgcolor: transparent"] 910 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,040 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,170 [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]AREA 2[/TD]
[TD="class: xl68, bgcolor: transparent"]TERRITORY 3[/TD]
[TD="class: xl71, bgcolor: transparent"]BRAND C[/TD]
[TD="class: xl69, bgcolor: transparent"] 660 [/TD]
[TD="class: xl70, bgcolor: transparent"] 790 [/TD]
[TD="class: xl70, bgcolor: transparent"] 920 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,050 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,180 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,310 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,440 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,570 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,700 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,830 [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]AREA 2[/TD]
[TD="class: xl68, bgcolor: transparent"]TERRITORY 3[/TD]
[TD="class: xl71, bgcolor: transparent"]BRAND D[/TD]
[TD="class: xl69, bgcolor: transparent"] - [/TD]
[TD="class: xl70, bgcolor: transparent"] 130 [/TD]
[TD="class: xl70, bgcolor: transparent"] 260 [/TD]
[TD="class: xl70, bgcolor: transparent"] 390 [/TD]
[TD="class: xl70, bgcolor: transparent"] 520 [/TD]
[TD="class: xl70, bgcolor: transparent"] 650 [/TD]
[TD="class: xl70, bgcolor: transparent"] 780 [/TD]
[TD="class: xl70, bgcolor: transparent"] 910 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,040 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,170 [/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]AREA 2[/TD]
[TD="class: xl68, bgcolor: transparent"]TERRITORY 3[/TD]
[TD="class: xl71, bgcolor: transparent"]BRAND D[/TD]
[TD="class: xl69, bgcolor: transparent"] 140 [/TD]
[TD="class: xl70, bgcolor: transparent"] 270 [/TD]
[TD="class: xl70, bgcolor: transparent"] 400 [/TD]
[TD="class: xl70, bgcolor: transparent"] 530 [/TD]
[TD="class: xl70, bgcolor: transparent"] 660 [/TD]
[TD="class: xl70, bgcolor: transparent"] 790 [/TD]
[TD="class: xl70, bgcolor: transparent"] 920 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,050 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,180 [/TD]
[TD="class: xl70, bgcolor: transparent"] 1,310 [/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
If I'm understanding correctly maybe something like....

I forgot to mention that you would need to replace the "-" with a 0

S2=SUMPRODUCT((O2&P2&Q2=Sheet1!A2:A33&Sheet1!B2:B33&Sheet1!C2:C33)*(R2=Sheet1!D1:M1)*(Sheet1!D2:M33))


[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/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]
[TD]
O​
[/TD]
[TD]
P​
[/TD]
[TD]
Q​
[/TD]
[TD]
R​
[/TD]
[TD]
S​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD]Area[/TD]
[TD]Territory[/TD]
[TD]Brand[/TD]
[TD]
1-Jan-17​
[/TD]
[TD]
2-Jan-17​
[/TD]
[TD]
3-Jan-17​
[/TD]
[TD]
4-Jan-17​
[/TD]
[TD]
5-Jan-17​
[/TD]
[TD]
6-Jan-17​
[/TD]
[TD]
7-Jan-17​
[/TD]
[TD]
8-Jan-17​
[/TD]
[TD]
9-Jan-17​
[/TD]
[TD]
10-Jan-17​
[/TD]
[TD][/TD]
[TD]Area[/TD]
[TD]Territory[/TD]
[TD]Brand[/TD]
[TD]Date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]AREA 1[/TD]
[TD]TERRITORY 1[/TD]
[TD]BRAND A[/TD]
[TD]
1,160​
[/TD]
[TD]
1,290​
[/TD]
[TD]
1,420​
[/TD]
[TD]
1,550​
[/TD]
[TD]
1,680​
[/TD]
[TD]
1,810​
[/TD]
[TD]
1,940​
[/TD]
[TD]
2,070​
[/TD]
[TD]
2,200​
[/TD]
[TD]
2,330​
[/TD]
[TD][/TD]
[TD]AREA 1[/TD]
[TD]TERRITORY 1[/TD]
[TD]BRAND A[/TD]
[TD]
1-Jan-17​
[/TD]
[TD]
3740​
[/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]AREA 1[/TD]
[TD]TERRITORY 1[/TD]
[TD]BRAND A[/TD]
[TD]
2,500​
[/TD]
[TD]
2,630​
[/TD]
[TD]
2,760​
[/TD]
[TD]
2,890​
[/TD]
[TD]
3,020​
[/TD]
[TD]
3,150​
[/TD]
[TD]
3,280​
[/TD]
[TD]
3,410​
[/TD]
[TD]
3,540​
[/TD]
[TD]
3,670​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]AREA 1[/TD]
[TD]TERRITORY 1[/TD]
[TD]BRAND B[/TD]
[TD]
1,180​
[/TD]
[TD]
1,310​
[/TD]
[TD]
1,440​
[/TD]
[TD]
1,570​
[/TD]
[TD]
1,700​
[/TD]
[TD]
1,830​
[/TD]
[TD]
1,960​
[/TD]
[TD]
2,090​
[/TD]
[TD]
2,220​
[/TD]
[TD]
2,350​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD]AREA 1[/TD]
[TD]TERRITORY 1[/TD]
[TD]BRAND B[/TD]
[TD]
2,500​
[/TD]
[TD]
2,630​
[/TD]
[TD]
2,760​
[/TD]
[TD]
2,890​
[/TD]
[TD]
3,020​
[/TD]
[TD]
3,150​
[/TD]
[TD]
3,280​
[/TD]
[TD]
3,410​
[/TD]
[TD]
3,540​
[/TD]
[TD]
3,670​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
6​
[/TD]
[TD]AREA 1[/TD]
[TD]TERRITORY 1[/TD]
[TD]BRAND C[/TD]
[TD]
1,180​
[/TD]
[TD]
1,310​
[/TD]
[TD]
1,440​
[/TD]
[TD]
1,570​
[/TD]
[TD]
1,700​
[/TD]
[TD]
1,830​
[/TD]
[TD]
1,960​
[/TD]
[TD]
2,090​
[/TD]
[TD]
2,220​
[/TD]
[TD]
2,350​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
7​
[/TD]
[TD]AREA 1[/TD]
[TD]TERRITORY 1[/TD]
[TD]BRAND C[/TD]
[TD]
0​
[/TD]
[TD]
130​
[/TD]
[TD]
260​
[/TD]
[TD]
390​
[/TD]
[TD]
520​
[/TD]
[TD]
650​
[/TD]
[TD]
780​
[/TD]
[TD]
910​
[/TD]
[TD]
1,040​
[/TD]
[TD]
1,170​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
8​
[/TD]
[TD]AREA 1[/TD]
[TD]TERRITORY 1[/TD]
[TD]BRAND D[/TD]
[TD]
0​
[/TD]
[TD]
130​
[/TD]
[TD]
260​
[/TD]
[TD]
390​
[/TD]
[TD]
520​
[/TD]
[TD]
650​
[/TD]
[TD]
780​
[/TD]
[TD]
910​
[/TD]
[TD]
1,040​
[/TD]
[TD]
1,170​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
9​
[/TD]
[TD]AREA 1[/TD]
[TD]TERRITORY 1[/TD]
[TD]BRAND D[/TD]
[TD]
400​
[/TD]
[TD]
530​
[/TD]
[TD]
660​
[/TD]
[TD]
790​
[/TD]
[TD]
920​
[/TD]
[TD]
1,050​
[/TD]
[TD]
1,180​
[/TD]
[TD]
1,310​
[/TD]
[TD]
1,440​
[/TD]
[TD]
1,570​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
10​
[/TD]
[TD]AREA 1[/TD]
[TD]TERRITORY 1[/TD]
[TD]BRAND A[/TD]
[TD]
80​
[/TD]
[TD]
210​
[/TD]
[TD]
340​
[/TD]
[TD]
470​
[/TD]
[TD]
600​
[/TD]
[TD]
730​
[/TD]
[TD]
860​
[/TD]
[TD]
990​
[/TD]
[TD]
1,120​
[/TD]
[TD]
1,250​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
11​
[/TD]
[TD]AREA 1[/TD]
[TD]TERRITORY 1[/TD]
[TD]BRAND A[/TD]
[TD]
0​
[/TD]
[TD]
130​
[/TD]
[TD]
260​
[/TD]
[TD]
390​
[/TD]
[TD]
520​
[/TD]
[TD]
650​
[/TD]
[TD]
780​
[/TD]
[TD]
910​
[/TD]
[TD]
1,040​
[/TD]
[TD]
1,170​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
12​
[/TD]
[TD]AREA 1[/TD]
[TD]TERRITORY 1[/TD]
[TD]BRAND B[/TD]
[TD]
10​
[/TD]
[TD]
140​
[/TD]
[TD]
270​
[/TD]
[TD]
400​
[/TD]
[TD]
530​
[/TD]
[TD]
660​
[/TD]
[TD]
790​
[/TD]
[TD]
920​
[/TD]
[TD]
1,050​
[/TD]
[TD]
1,180​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
13​
[/TD]
[TD]AREA 1[/TD]
[TD]TERRITORY 1[/TD]
[TD]BRAND B[/TD]
[TD]
620​
[/TD]
[TD]
750​
[/TD]
[TD]
880​
[/TD]
[TD]
1,010​
[/TD]
[TD]
1,140​
[/TD]
[TD]
1,270​
[/TD]
[TD]
1,400​
[/TD]
[TD]
1,530​
[/TD]
[TD]
1,660​
[/TD]
[TD]
1,790​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
14​
[/TD]
[TD]AREA 1[/TD]
[TD]TERRITORY 1[/TD]
[TD]BRAND C[/TD]
[TD]
630​
[/TD]
[TD]
760​
[/TD]
[TD]
890​
[/TD]
[TD]
1,020​
[/TD]
[TD]
1,150​
[/TD]
[TD]
1,280​
[/TD]
[TD]
1,410​
[/TD]
[TD]
1,540​
[/TD]
[TD]
1,670​
[/TD]
[TD]
1,800​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
15​
[/TD]
[TD]AREA 1[/TD]
[TD]TERRITORY 1[/TD]
[TD]BRAND C[/TD]
[TD]
120​
[/TD]
[TD]
250​
[/TD]
[TD]
380​
[/TD]
[TD]
510​
[/TD]
[TD]
640​
[/TD]
[TD]
770​
[/TD]
[TD]
900​
[/TD]
[TD]
1,030​
[/TD]
[TD]
1,160​
[/TD]
[TD]
1,290​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
16​
[/TD]
[TD]AREA 1[/TD]
[TD]TERRITORY 1[/TD]
[TD]BRAND D[/TD]
[TD]
100​
[/TD]
[TD]
230​
[/TD]
[TD]
360​
[/TD]
[TD]
490​
[/TD]
[TD]
620​
[/TD]
[TD]
750​
[/TD]
[TD]
880​
[/TD]
[TD]
1,010​
[/TD]
[TD]
1,140​
[/TD]
[TD]
1,270​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
17​
[/TD]
[TD]AREA 1[/TD]
[TD]TERRITORY 1[/TD]
[TD]BRAND D[/TD]
[TD]
0​
[/TD]
[TD]
130​
[/TD]
[TD]
260​
[/TD]
[TD]
390​
[/TD]
[TD]
520​
[/TD]
[TD]
650​
[/TD]
[TD]
780​
[/TD]
[TD]
910​
[/TD]
[TD]
1,040​
[/TD]
[TD]
1,170​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
18​
[/TD]
[TD]AREA 2[/TD]
[TD]TERRITORY 2[/TD]
[TD]BRAND A[/TD]
[TD]
910​
[/TD]
[TD]
1,040​
[/TD]
[TD]
1,170​
[/TD]
[TD]
1,300​
[/TD]
[TD]
1,430​
[/TD]
[TD]
1,560​
[/TD]
[TD]
1,690​
[/TD]
[TD]
1,820​
[/TD]
[TD]
1,950​
[/TD]
[TD]
2,080​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
19​
[/TD]
[TD]AREA 2[/TD]
[TD]TERRITORY 2[/TD]
[TD]BRAND A[/TD]
[TD]
2,160​
[/TD]
[TD]
2,290​
[/TD]
[TD]
2,420​
[/TD]
[TD]
2,550​
[/TD]
[TD]
2,680​
[/TD]
[TD]
2,810​
[/TD]
[TD]
2,940​
[/TD]
[TD]
3,070​
[/TD]
[TD]
3,200​
[/TD]
[TD]
3,330​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
20​
[/TD]
[TD]AREA 2[/TD]
[TD]TERRITORY 2[/TD]
[TD]BRAND B[/TD]
[TD]
900​
[/TD]
[TD]
1,030​
[/TD]
[TD]
1,160​
[/TD]
[TD]
1,290​
[/TD]
[TD]
1,420​
[/TD]
[TD]
1,550​
[/TD]
[TD]
1,680​
[/TD]
[TD]
1,810​
[/TD]
[TD]
1,940​
[/TD]
[TD]
2,070​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
21​
[/TD]
[TD]AREA 2[/TD]
[TD]TERRITORY 2[/TD]
[TD]BRAND B[/TD]
[TD]
8,040​
[/TD]
[TD]
8,170​
[/TD]
[TD]
8,300​
[/TD]
[TD]
8,430​
[/TD]
[TD]
8,560​
[/TD]
[TD]
8,690​
[/TD]
[TD]
8,820​
[/TD]
[TD]
8,950​
[/TD]
[TD]
9,080​
[/TD]
[TD]
9,210​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
22​
[/TD]
[TD]AREA 2[/TD]
[TD]TERRITORY 2[/TD]
[TD]BRAND C[/TD]
[TD]
1,310​
[/TD]
[TD]
1,440​
[/TD]
[TD]
1,570​
[/TD]
[TD]
1,700​
[/TD]
[TD]
1,830​
[/TD]
[TD]
1,960​
[/TD]
[TD]
2,090​
[/TD]
[TD]
2,220​
[/TD]
[TD]
2,350​
[/TD]
[TD]
2,480​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
23​
[/TD]
[TD]AREA 2[/TD]
[TD]TERRITORY 2[/TD]
[TD]BRAND C[/TD]
[TD]
6,560​
[/TD]
[TD]
6,690​
[/TD]
[TD]
6,820​
[/TD]
[TD]
6,950​
[/TD]
[TD]
7,080​
[/TD]
[TD]
7,210​
[/TD]
[TD]
7,340​
[/TD]
[TD]
7,470​
[/TD]
[TD]
7,600​
[/TD]
[TD]
7,730​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
24​
[/TD]
[TD]AREA 2[/TD]
[TD]TERRITORY 2[/TD]
[TD]BRAND D[/TD]
[TD]
420​
[/TD]
[TD]
550​
[/TD]
[TD]
680​
[/TD]
[TD]
810​
[/TD]
[TD]
940​
[/TD]
[TD]
1,070​
[/TD]
[TD]
1,200​
[/TD]
[TD]
1,330​
[/TD]
[TD]
1,460​
[/TD]
[TD]
1,590​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
25​
[/TD]
[TD]AREA 2[/TD]
[TD]TERRITORY 2[/TD]
[TD]BRAND D[/TD]
[TD]
460​
[/TD]
[TD]
590​
[/TD]
[TD]
720​
[/TD]
[TD]
850​
[/TD]
[TD]
980​
[/TD]
[TD]
1,110​
[/TD]
[TD]
1,240​
[/TD]
[TD]
1,370​
[/TD]
[TD]
1,500​
[/TD]
[TD]
1,630​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
26​
[/TD]
[TD]AREA 2[/TD]
[TD]TERRITORY 3[/TD]
[TD]BRAND A[/TD]
[TD]
0​
[/TD]
[TD]
130​
[/TD]
[TD]
260​
[/TD]
[TD]
390​
[/TD]
[TD]
520​
[/TD]
[TD]
650​
[/TD]
[TD]
780​
[/TD]
[TD]
910​
[/TD]
[TD]
1,040​
[/TD]
[TD]
1,170​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
27​
[/TD]
[TD]AREA 2[/TD]
[TD]TERRITORY 3[/TD]
[TD]BRAND A[/TD]
[TD]
200​
[/TD]
[TD]
330​
[/TD]
[TD]
460​
[/TD]
[TD]
590​
[/TD]
[TD]
720​
[/TD]
[TD]
850​
[/TD]
[TD]
980​
[/TD]
[TD]
1,110​
[/TD]
[TD]
1,240​
[/TD]
[TD]
1,370​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
28​
[/TD]
[TD]AREA 2[/TD]
[TD]TERRITORY 3[/TD]
[TD]BRAND B[/TD]
[TD]
0​
[/TD]
[TD]
130​
[/TD]
[TD]
260​
[/TD]
[TD]
390​
[/TD]
[TD]
520​
[/TD]
[TD]
650​
[/TD]
[TD]
780​
[/TD]
[TD]
910​
[/TD]
[TD]
1,040​
[/TD]
[TD]
1,170​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
29​
[/TD]
[TD]AREA 2[/TD]
[TD]TERRITORY 3[/TD]
[TD]BRAND B[/TD]
[TD]
1,200​
[/TD]
[TD]
1,330​
[/TD]
[TD]
1,460​
[/TD]
[TD]
1,590​
[/TD]
[TD]
1,720​
[/TD]
[TD]
1,850​
[/TD]
[TD]
1,980​
[/TD]
[TD]
2,110​
[/TD]
[TD]
2,240​
[/TD]
[TD]
2,370​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
30​
[/TD]
[TD]AREA 2[/TD]
[TD]TERRITORY 3[/TD]
[TD]BRAND C[/TD]
[TD]
0​
[/TD]
[TD]
130​
[/TD]
[TD]
260​
[/TD]
[TD]
390​
[/TD]
[TD]
520​
[/TD]
[TD]
650​
[/TD]
[TD]
780​
[/TD]
[TD]
910​
[/TD]
[TD]
1,040​
[/TD]
[TD]
1,170​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
31​
[/TD]
[TD]AREA 2[/TD]
[TD]TERRITORY 3[/TD]
[TD]BRAND C[/TD]
[TD]
660​
[/TD]
[TD]
790​
[/TD]
[TD]
920​
[/TD]
[TD]
1,050​
[/TD]
[TD]
1,180​
[/TD]
[TD]
1,310​
[/TD]
[TD]
1,440​
[/TD]
[TD]
1,570​
[/TD]
[TD]
1,700​
[/TD]
[TD]
1,830​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
32​
[/TD]
[TD]AREA 2[/TD]
[TD]TERRITORY 3[/TD]
[TD]BRAND D[/TD]
[TD]
0​
[/TD]
[TD]
130​
[/TD]
[TD]
260​
[/TD]
[TD]
390​
[/TD]
[TD]
520​
[/TD]
[TD]
650​
[/TD]
[TD]
780​
[/TD]
[TD]
910​
[/TD]
[TD]
1,040​
[/TD]
[TD]
1,170​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
33​
[/TD]
[TD]AREA 2[/TD]
[TD]TERRITORY 3[/TD]
[TD]BRAND D[/TD]
[TD]
140​
[/TD]
[TD]
270​
[/TD]
[TD]
400​
[/TD]
[TD]
530​
[/TD]
[TD]
660​
[/TD]
[TD]
790​
[/TD]
[TD]
920​
[/TD]
[TD]
1,050​
[/TD]
[TD]
1,180​
[/TD]
[TD]
1,310​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
if you didn't want to replace the "-", then something like

=SUM(IF(Sheet2!A1&Sheet2!B1&Sheet2!C1=Sheet1!A2:A33&Sheet1!B2:B33&Sheet1!C2:C33,IF(Sheet1!D1:M1=Sheet2!D1,Sheet1!D2:M33))) control shift enter
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Index Match function List based on date
Replies
7
Views
241
Fluff
Fluff
M
  • Solved
VBA Transpose data from one sheet into another
Replies
9
Views
573
madi1004
M
M
  • Solved
Help with separating Text String from a Excel Col
Replies
3
Views
591
Peter_SSs
Peter_SSs
I
  • Solved
INDEX, MATCH & INDIRECT- Still output comes for those horizontal & vertical ref not there in the table
Replies
9
Views
676
Fluff
Fluff
J
  • Question Question
Need some help with permutation code, convert from one cell to a range
Replies
3
Views
425
johnnyL
J

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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