array function across sheets

[EngMarine]

hi all

i have 31 sheets in my excel file and i want to sum the results from this function when applied on each sheet

OK to be more clear i have values of 1 to 31 written in cells from M6 to AQ6

i used to sum the formulas by repeating them and change the $M$6 to $N$6 and "1" to "2" .... etc

example:

=IFERROR(VLOOKUP($C14,OFFSET(INDIRECT($M$6&"!"&ADDRESS(MATCH($C14,'1'!$C:$C,0),3,4,1)),,,,100),MATCH(G$12,OFFSET(INDIRECT($M$6&"!"&ADDRESS(MATCH($C14,'1'!$C:$C,0),3,4,1)),,,,100),0)+1,FALSE),0)
+IFERROR(VLOOKUP($C14,OFFSET(INDIRECT($N$6&"!"&ADDRESS(MATCH($C14,'2'!$C:$C,0),3,4,1)),,,,100),MATCH(G$12,OFFSET(INDIRECT($N$6&"!"&ADDRESS(MATCH($C14,'2'!$C:$C,0),3,4,1)),,,,100),0)+1,FALSE),0)
+......etc up to 31 sheets

it's very long and painful method to sum and it takes a lot of time if i want to edit it

so is there any way i can array this function across the 31 sheets in one move ?

and i'm sorry to say i don't need macros

thanks in advance

have a nice day

 

[Aladin Akyurek]

Sorry I can't understand what u mean , can u explain what u mean by revelant range ?

You have data in 1, no? I want to know where that data is located? In A1:E20 maybe? Something like A1:E20 is often called a range.

 

[EngMarine]

You have data in 1, no? I want to know where that data is located? In A1:E20 maybe? Something like A1:E20 is often called a range.

My data are located in c15:ai25 but users can insert rows as much as needed so it may reach ai160 or sometime like this

 

[Aladin Akyurek]

My data are located in c15:ai25 but users can insert rows as much as needed so it may reach ai160 or sometime like this

Does C15:AI15 consist of headers?

 

[EngMarine]

Does C15:AI15 consist of headers?

No

 

[Aladin Akyurek]

No

No headers at all? Perhaps in C14:AI14?

 

[EngMarine]

No headers at all? Perhaps in C14:AI14?

oh yea i have headers in C14 : Ai14

 

[Aladin Akyurek]

oh yea i have headers in C14 : Ai14

So we have C14:AI25.

What is the value we are looking for in C15:AI15?

What is the value we are looking for in C14:AI14?

 

[EngMarine]

So we have C14:AI25.

What is the value we are looking for in C15:AI15?

What is the value we are looking for in C14:AI14?

actually i'm not looking for anything in C14:AI4

i'm using vlookup function but with modification which enables me to find a value which in same row with two texts ( not only 1 like normal vlookup function )

IFERROR(VLOOKUP($A14,OFFSET(INDIRECT($M$6&"!"&ADDRESS(MATCH($C14,'1'!$C:$C,0),3,4,1)),,,,100),MATCH(B$12,OFFSET(INDIRECT($M$6&"!"&ADDRESS(MATCH($C14,'1'!$C:$C,0),3,4,1)),,,,100),0)+1,FALSE),0)

so the function search for certain text ( which in A14 ) in Column C and once it find it (Assume it was Cell C20) , it starts to search in it's row ( Raw Number 20 ) for the text which located in cell B12 (Assume it was in AF20) and then it gets the number in front of it ( which in cell AG20 )
if it didn't find any of those two texts it return 0

 

[Aladin Akyurek]

So we have C14:AI25.

What is the value we are looking for in C15:AI15?

What is the value we are looking for in C14:AI14?

actually i'm not looking for anything in C14:AI4

i'm using vlookup function but with modification which enables me to find a value which in same row with two texts ( not only 1 like normal vlookup function )

IFERROR(VLOOKUP($A14,OFFSET(INDIRECT($M$6&"!"&ADDRESS(MATCH($C14,'1'!$C:$C,0),3,4,1)),,,,100),MATCH(B$12,OFFSET(INDIRECT($M$6&"!"&ADDRESS(MATCH($C14,'1'!$C:$C,0),3,4,1)),,,,100),0)+1,FALSE),0)

so the function search for certain text ( which in A14 ) in Column C and once it find it (Assume it was Cell C20) , it starts to search in it's row ( Raw Number 20 ) for the text which located in cell B12 (Assume it was in AF20) and then it gets the number in front of it ( which in cell AG20 )
if it didn't find any of those two texts it return 0

You don't have to re-post this continually...

So we have...

[A] C14:AI25 (make that C14:AI200) with C14:AI14 housing headers on each sheet fro 1 to 31.

The sheet names are in M6:AQ6 of the summary sheet.

[C] We have in A14 of the summary sheet a value to look for in C15:C200 of every sheet.

[D] We have in B12 of the summary sheet to look for in C14:A114 of every sheet.

If so, try:

=SUMPRODUCT(SUMIFS(INDEX(INDIRECT("'"&$M$6:$AQ$6&"'!C14:AI200"),0,
  MATCH(B12,INDEX(INDIRECT("'"&$M$6:$AQ$6&"'!C14:AI14"),1,0),0)),
  INDIRECT("'"&$M$6:$AQ$6&"'!C15:C200"),A14))

 

[EngMarine]

You don't have to re-post this continually...

So we have...

[A] C14:AI25 (make that C14:AI200) with C14:AI14 housing headers on each sheet fro 1 to 31.

The sheet names are in M6:AQ6 of the summary sheet.

[C] We have in A14 of the summary sheet a value to look for in C15:C200 of every sheet.

[D] We have in B12 of the summary sheet to look for in C14:A114 of every sheet.

If so, try:

=SUMPRODUCT(SUMIFS(INDEX(INDIRECT("'"&$M$6:$AQ$6&"'!C14:AI200"),0,
  MATCH(B12,INDEX(INDIRECT("'"&$M$6:$AQ$6&"'!C14:AI14"),1,0),0)),
  INDIRECT("'"&$M$6:$AQ$6&"'!C15:C200"),A14))

for sumifs function i should select sum range then criteria range then criteria
the index function return single value not array

u sure about it ?