Excel Array Formula

ziad alsayed · Feb 26, 2011

dear all

please is an array formula (CTRL+SHIFT+ENTER), is it possible to make i smaller

Appreciate any help

Code:

=SUM(VLOOKUP($A5,'PHC 2011'!$A:$CH,MATCH(CONCATENATE($A$1,C$4),CONCATENATE('PHC 2011'!$3:$3,'PHC 2011'!$4:$4),0),0),VLOOKUP($A5,'Apapa 2011'!$A:$CH,MATCH(CONCATENATE($A$1,C$4),CONCATENATE('Apapa 2011'!$3:$3,'Apapa 2011'!$4:$4),0),0),VLOOKUP($A5,'VI 2011'!$A:$CH,MATCH(CONCATENATE($A$1,C$4),CONCATENATE('VI 2011'!$3:$3,'VI 2011'!$4:$4),0),0),VLOOKUP($A5,'Kano 2011'!$A:$CH,MATCH(CONCATENATE($A$1,C$4),CONCATENATE('Kano 2011'!$3:$3,'Kano 2011'!$4:$4),0),0),VLOOKUP($A5,'Ikeja 2011'!$A:$CH,MATCH(CONCATENATE($A$1,C$4),CONCATENATE('Ikeja 2011'!$3:$3,'Ikeja 2011'!$4:$4),0),0))

jeffreybrown · Feb 26, 2011

Hard to say what would work best, however, you have repeated the same formula five times. Maybe some more explanation is required.

What you could use is Indirect. With the first formula I have used F1 to toggle between sheets. Don't know if you want one sheet summed at a time or all. If you want all then the second version could help. Find a range somewhere on your spreadsheet and list the five sheets and then name the range SheetList.

Code:

=SUM(
     VLOOKUP($A5,INDIRECT("'"&F1&"'!$A$1:$CH$65536"),
     MATCH(CONCATENATE($A$1,C$4),
     CONCATENATE(INDIRECT("'"&F1&"'!$A$3:$IV$4")),0),0))

Code:

=SUM(
     VLOOKUP($A5,INDIRECT("'"&SheetList&"'!$A$1:$CH$65536"),
     MATCH(CONCATENATE($A$1,C$4),
     CONCATENATE(INDIRECT("'"&SheetList&"'!$A$3:$IV$4")),0),0))

ziad alsayed · Feb 26, 2011

dear Jeff

thanks for the reply ,what i need is the second option, which will Sum the value from each sheet and put in a Sheet named "Nigeria".

the formula you sent is not working.

what i am doing is to sum from the Five sheets base on the criteria i Chooose in the "Nigeria" Sheet ( in cell A1).

the format is too big i posted below part of it , i hope it can help .
thanks again

Excel Workbook

A

B

C

1

2

PHC 2011

3

SIZE

Jan/11

4

Range

Model

MTN

5

L12.5

LPW3

39

6

L17.5

LPW4

39

7

L17.5DS

LPW4

39

8

L17.5LR

LPW4

39

9

P13.5E2

403C-15G

39

10

P18E2

404C-22G1

39

11

P22E2

404C-22G2

39

12

P27

3.1524

39

PHC 2011

Excel 2010

Excel Workbook

A

B

C

1

2

Apapa 2011

3

SIZE

Jan/11

4

Range

Model

MTN

5

L12.5

LPW3

2

6

L17.5

LPW4

2

7

L17.5DS

LPW4

2

8

L17.5LR

LPW4

2

9

P13.5E2

403C-15G

2

10

P18E2

404C-22G1

2

11

P22E2

404C-22G2

2

12

P27

3.1524

2

Apapa 2011

Excel 2010

Excel Workbook

A

B

C

1

2

VI 2011

3

SIZE

Jan/11

4

Range

Model

MTN

5

L12.5

LPW3

51

6

L17.5

LPW4

51

7

L17.5DS

LPW4

51

8

L17.5LR

LPW4

51

9

P13.5E2

403C-15G

51

10

P18E2

404C-22G1

51

11

P22E2

404C-22G2

51

12

P27

3.1524

51

VI 2011

Excel 2010

Excel Workbook

A

B

C

1

2

kano 2011

3

SIZE

Jan/11

4

Range

Model

MTN

5

L12.5

LPW3

26

6

L17.5

LPW4

26

7

L17.5DS

LPW4

26

8

L17.5LR

LPW4

26

9

P13.5E2

403C-15G

26

10

P18E2

404C-22G1

26

11

P22E2

404C-22G2

26

12

P27

3.1524

26

Kano 2011

Excel 2010

Excel Workbook

A

B

C

1

2

Ikeja 2011

3

SIZE

####

4

Range

Model

MTN

5

L12.5

LPW3

14

6

L17.5

LPW4

14

7

L17.5DS

LPW4

14

8

L17.5LR

LPW4

14

9

P13.5E2

403C-15G

14

10

P18E2

404C-22G1

14

11

P22E2

404C-22G2

14

12

P27

3.1524

14

Ikeja 2011

Excel 2010

Excel Workbook

A

B

C

1

Jan/11

2

NIGERIA

GENSETS

3

SIZE

Nb. Of Gensets

4

Range

Model

MTN

5

L12.5

LPW3

132

6

L17.5

LPW4

132

7

L17.5DS

LPW4

132

8

L17.5LR

LPW4

132

9

P13.5E2

403C-15G

132

10

P18E2

404C-22G1

132

11

P22E2

404C-22G2

132

12

P27

3.1524

132

Nigeria

Excel 2010

Cell Formulas
Range	Formula
A5	='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!A5
A6	='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!A6
A9	='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!A9
A10	='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!A10
A11	='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!A11
A12	='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!A12
B5	='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!B5
B6	='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!B6
B9	='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!B9
B10	='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!B10
B11	='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!B11
B12	='C:\Users\dv7\Desktop\[MC Summary 1101.xlsx]Ikeja'!B12

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

jeffreybrown · Feb 26, 2011

For starters how about...

Cell C5 and drag down...
=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!A:A"),A5,INDIRECT("'"&SheetList&"'!C:C")))

Make sure you create a named range with the five sheet names a call in SheetList

Also, just as a side note. When using Jeanie you can specify which cells to produce as formulas. Since you formulas in C5:C12 and basically duplicates you need only show one. Just saves spaces.

ziad alsayed · Feb 26, 2011

dear Jeff

Maybe i didn't explain well, though the "formula works for one month". but it is not what i need.

below is what i have in each sheet and for 12 month(i just posted one month). i have to meet two criteria to get the data into Nigeria Sheet.( i just posted PHC 2011) as example.

Excel Workbook

A

B

C

D

E

F

G

H

I

1

2

PHC 2011

3

SIZE

Jan/11

4

Range

Model

MTN

Star

Zain

SIMS

MC

Oth.

Total

5

L12.5

LPW3

39

234

6

L17.5

LPW4

39

234

7

L17.5DS

LPW4

39

234

8

L17.5LR

LPW4

39

234

9

P13.5E2

403C-15G

39

234

10

P18E2

404C-22G1

39

234

11

P22E2

404C-22G2

39

234

12

P27

3.1524

39

234

PHC 2011

Excel 2010

Cell Formulas
Range	Formula
I5	=SUM(C5:H5)
I6	=SUM(C6:H6)
I7	=SUM(C7:H7)
I8	=SUM(C8:H8)
I9	=SUM(C9:H9)
I10	=SUM(C10:H10)
I11	=SUM(C11:H11)
I12	=SUM(C12:H12)

also in Nigeria below is what i have only

Excel Workbook

A

B

C

D

E

F

G

H

I

1

Jan/11

2

NIGERIA

GENSETS

3

SIZE

Nb. Of Gensets

4

Range

Model

MTN

Star

Zain

SIMS

MC

Oth.

Total

5

L12.5

LPW3

132

792

6

L17.5

LPW4

132

792

7

L17.5DS

LPW4

132

792

8

L17.5LR

LPW4

132

792

9

P13.5E2

403C-15G

132

792

10

P18E2

404C-22G1

132

792

11

P22E2

404C-22G2

132

792

12

P27

3.1524

132

792

Nigeria

Excel 2010

i hope it is clear.
thanks for the assistance

jeffreybrown · Feb 26, 2011

I was thinking that Sumifs might be the next option, but I'm having trouble making it work.

Maybe somebody else can help us out.

ziad alsayed · Feb 26, 2011

Thanks Jeffs for your assistance and that time you put.

i am sure somebody can assist us

, i hope so.

take care.

ziad alsayed · Feb 26, 2011

dear all

Any idea please??

jeffreybrown · Feb 26, 2011

Hi Ziad,

I somehow feel I may not have all the right information here for the correct solution. With the data you have posted it seems all the table are the same so you are just making a summary sheet.

If this is so, why wouldn't this work.

=SUM('VI 2011:Ikeja 2011'!C5)

ziad alsayed · Feb 26, 2011

hi jeff

i wish if it was easy like that

, still not what i need. if it is ok by you i have no problem sending you the file.

please advise.

Excel Array Formula

ziad alsayed

Well-known Member

Excel Facts

jeffreybrown

Well-known Member

ziad alsayed

Well-known Member

jeffreybrown

Well-known Member

ziad alsayed

Well-known Member

jeffreybrown

Well-known Member

ziad alsayed

Well-known Member

ziad alsayed

Well-known Member

jeffreybrown

Well-known Member

ziad alsayed

Well-known Member

Similar threads

Forum statistics

Share this page

Excel Array Formula

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock