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

[Morshed_Dhaka]

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.

Area

Territory

Brand

1-Jan-17

2-Jan-17

3-Jan-17

4-Jan-17

5-Jan-17

6-Jan-17

7-Jan-17

8-Jan-17

9-Jan-17

10-Jan-17

AREA 1

TERRITORY 1

BRAND A

AREA 1

TERRITORY 1

BRAND B

AREA 1

TERRITORY 1

BRAND C

AREA 1

TERRITORY 1

BRAND D

AREA 2

TERRITORY 1

BRAND A

AREA 2

TERRITORY 1

BRAND B

AREA 2

TERRITORY 1

BRAND C

AREA 2

TERRITORY 1

BRAND D

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

Area	Territory	Brand	1-Jan-17	2-Jan-17	3-Jan-17	4-Jan-17	5-Jan-17	6-Jan-17	7-Jan-17	8-Jan-17	9-Jan-17	10-Jan-17
AREA 1	TERRITORY 1	BRAND A	1,160	1,290	1,420	1,550	1,680	1,810	1,940	2,070	2,200	2,330
AREA 1	TERRITORY 1	BRAND A	2,500	2,630	2,760	2,890	3,020	3,150	3,280	3,410	3,540	3,670
AREA 1	TERRITORY 1	BRAND B	1,180	1,310	1,440	1,570	1,700	1,830	1,960	2,090	2,220	2,350
AREA 1	TERRITORY 1	BRAND B	2,500	2,630	2,760	2,890	3,020	3,150	3,280	3,410	3,540	3,670
AREA 1	TERRITORY 1	BRAND C	1,180	1,310	1,440	1,570	1,700	1,830	1,960	2,090	2,220	2,350
AREA 1	TERRITORY 1	BRAND C	-	130	260	390	520	650	780	910	1,040	1,170
AREA 1	TERRITORY 1	BRAND D	-	130	260	390	520	650	780	910	1,040	1,170
AREA 1	TERRITORY 1	BRAND D	400	530	660	790	920	1,050	1,180	1,310	1,440	1,570
AREA 1	TERRITORY 1	BRAND A	80	210	340	470	600	730	860	990	1,120	1,250
AREA 1	TERRITORY 1	BRAND A	-	130	260	390	520	650	780	910	1,040	1,170
AREA 1	TERRITORY 1	BRAND B	10	140	270	400	530	660	790	920	1,050	1,180
AREA 1	TERRITORY 1	BRAND B	620	750	880	1,010	1,140	1,270	1,400	1,530	1,660	1,790
AREA 1	TERRITORY 1	BRAND C	630	760	890	1,020	1,150	1,280	1,410	1,540	1,670	1,800
AREA 1	TERRITORY 1	BRAND C	120	250	380	510	640	770	900	1,030	1,160	1,290
AREA 1	TERRITORY 1	BRAND D	100	230	360	490	620	750	880	1,010	1,140	1,270
AREA 1	TERRITORY 1	BRAND D	-	130	260	390	520	650	780	910	1,040	1,170
AREA 2	TERRITORY 2	BRAND A	910	1,040	1,170	1,300	1,430	1,560	1,690	1,820	1,950	2,080
AREA 2	TERRITORY 2	BRAND A	2,160	2,290	2,420	2,550	2,680	2,810	2,940	3,070	3,200	3,330
AREA 2	TERRITORY 2	BRAND B	900	1,030	1,160	1,290	1,420	1,550	1,680	1,810	1,940	2,070
AREA 2	TERRITORY 2	BRAND B	8,040	8,170	8,300	8,430	8,560	8,690	8,820	8,950	9,080	9,210
AREA 2	TERRITORY 2	BRAND C	1,310	1,440	1,570	1,700	1,830	1,960	2,090	2,220	2,350	2,480
AREA 2	TERRITORY 2	BRAND C	6,560	6,690	6,820	6,950	7,080	7,210	7,340	7,470	7,600	7,730
AREA 2	TERRITORY 2	BRAND D	420	550	680	810	940	1,070	1,200	1,330	1,460	1,590
AREA 2	TERRITORY 2	BRAND D	460	590	720	850	980	1,110	1,240	1,370	1,500	1,630
AREA 2	TERRITORY 3	BRAND A	-	130	260	390	520	650	780	910	1,040	1,170
AREA 2	TERRITORY 3	BRAND A	200	330	460	590	720	850	980	1,110	1,240	1,370
AREA 2	TERRITORY 3	BRAND B	-	130	260	390	520	650	780	910	1,040	1,170
AREA 2	TERRITORY 3	BRAND B	1,200	1,330	1,460	1,590	1,720	1,850	1,980	2,110	2,240	2,370
AREA 2	TERRITORY 3	BRAND C	-	130	260	390	520	650	780	910	1,040	1,170
AREA 2	TERRITORY 3	BRAND C	660	790	920	1,050	1,180	1,310	1,440	1,570	1,700	1,830
AREA 2	TERRITORY 3	BRAND D	-	130	260	390	520	650	780	910	1,040	1,170
AREA 2	TERRITORY 3	BRAND D	140	270	400	530	660	790	920	1,050	1,180	1,310

<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> </tbody>

 

[Weazel]

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))

Row\Col

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

Q​

R​

S​

1​

Area

Territory

Brand

1-Jan-17​

2-Jan-17​

3-Jan-17​

4-Jan-17​

5-Jan-17​

6-Jan-17​

7-Jan-17​

8-Jan-17​

9-Jan-17​

10-Jan-17​

Area

Territory

Brand

Date

2​

AREA 1

TERRITORY 1

BRAND A

1,160​

1,290​

1,420​

1,550​

1,680​

1,810​

1,940​

2,070​

2,200​

2,330​

AREA 1

TERRITORY 1

BRAND A

1-Jan-17​

3740​

3​

AREA 1

TERRITORY 1

BRAND A

2,500​

2,630​

2,760​

2,890​

3,020​

3,150​

3,280​

3,410​

3,540​

3,670​

4​

AREA 1

TERRITORY 1

BRAND B

1,180​

1,310​

1,440​

1,570​

1,700​

1,830​

1,960​

2,090​

2,220​

2,350​

5​

AREA 1

TERRITORY 1

BRAND B

2,500​

2,630​

2,760​

2,890​

3,020​

3,150​

3,280​

3,410​

3,540​

3,670​

6​

AREA 1

TERRITORY 1

BRAND C

1,180​

1,310​

1,440​

1,570​

1,700​

1,830​

1,960​

2,090​

2,220​

2,350​

7​

AREA 1

TERRITORY 1

BRAND C

0​

130​

260​

390​

520​

650​

780​

910​

1,040​

1,170​

8​

AREA 1

TERRITORY 1

BRAND D

0​

130​

260​

390​

520​

650​

780​

910​

1,040​

1,170​

9​

AREA 1

TERRITORY 1

BRAND D

400​

530​

660​

790​

920​

1,050​

1,180​

1,310​

1,440​

1,570​

10​

AREA 1

TERRITORY 1

BRAND A

80​

210​

340​

470​

600​

730​

860​

990​

1,120​

1,250​

11​

AREA 1

TERRITORY 1

BRAND A

0​

130​

260​

390​

520​

650​

780​

910​

1,040​

1,170​

12​

AREA 1

TERRITORY 1

BRAND B

10​

140​

270​

400​

530​

660​

790​

920​

1,050​

1,180​

13​

AREA 1

TERRITORY 1

BRAND B

620​

750​

880​

1,010​

1,140​

1,270​

1,400​

1,530​

1,660​

1,790​

14​

AREA 1

TERRITORY 1

BRAND C

630​

760​

890​

1,020​

1,150​

1,280​

1,410​

1,540​

1,670​

1,800​

15​

AREA 1

TERRITORY 1

BRAND C

120​

250​

380​

510​

640​

770​

900​

1,030​

1,160​

1,290​

16​

AREA 1

TERRITORY 1

BRAND D

100​

230​

360​

490​

620​

750​

880​

1,010​

1,140​

1,270​

17​

AREA 1

TERRITORY 1

BRAND D

0​

130​

260​

390​

520​

650​

780​

910​

1,040​

1,170​

18​

AREA 2

TERRITORY 2

BRAND A

910​

1,040​

1,170​

1,300​

1,430​

1,560​

1,690​

1,820​

1,950​

2,080​

19​

AREA 2

TERRITORY 2

BRAND A

2,160​

2,290​

2,420​

2,550​

2,680​

2,810​

2,940​

3,070​

3,200​

3,330​

20​

AREA 2

TERRITORY 2

BRAND B

900​

1,030​

1,160​

1,290​

1,420​

1,550​

1,680​

1,810​

1,940​

2,070​

21​

AREA 2

TERRITORY 2

BRAND B

8,040​

8,170​

8,300​

8,430​

8,560​

8,690​

8,820​

8,950​

9,080​

9,210​

22​

AREA 2

TERRITORY 2

BRAND C

1,310​

1,440​

1,570​

1,700​

1,830​

1,960​

2,090​

2,220​

2,350​

2,480​

23​

AREA 2

TERRITORY 2

BRAND C

6,560​

6,690​

6,820​

6,950​

7,080​

7,210​

7,340​

7,470​

7,600​

7,730​

24​

AREA 2

TERRITORY 2

BRAND D

420​

550​

680​

810​

940​

1,070​

1,200​

1,330​

1,460​

1,590​

25​

AREA 2

TERRITORY 2

BRAND D

460​

590​

720​

850​

980​

1,110​

1,240​

1,370​

1,500​

1,630​

26​

AREA 2

TERRITORY 3

BRAND A

0​

130​

260​

390​

520​

650​

780​

910​

1,040​

1,170​

27​

AREA 2

TERRITORY 3

BRAND A

200​

330​

460​

590​

720​

850​

980​

1,110​

1,240​

1,370​

28​

AREA 2

TERRITORY 3

BRAND B

0​

130​

260​

390​

520​

650​

780​

910​

1,040​

1,170​

29​

AREA 2

TERRITORY 3

BRAND B

1,200​

1,330​

1,460​

1,590​

1,720​

1,850​

1,980​

2,110​

2,240​

2,370​

30​

AREA 2

TERRITORY 3

BRAND C

0​

130​

260​

390​

520​

650​

780​

910​

1,040​

1,170​

31​

AREA 2

TERRITORY 3

BRAND C

660​

790​

920​

1,050​

1,180​

1,310​

1,440​

1,570​

1,700​

1,830​

32​

AREA 2

TERRITORY 3

BRAND D

0​

130​

260​

390​

520​

650​

780​

910​

1,040​

1,170​

33​

AREA 2

TERRITORY 3

BRAND D

140​

270​

400​

530​

660​

790​

920​

1,050​

1,180​

1,310​

<tbody>
</tbody>

 

[Weazel]

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