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]

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 ?

Did you try it all?

 

[EngMarine]

yaaaaaaaaaaaay it worked

it took me some time to understand the combination between Sumifs , index formulas

thanks alot for your time ... u saved me

 

[EngMarine]

Did you try it all?

sorry but i need onemore thing to make it perfect formula


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

Can i define the bold blue range with address and match function ?

 

[Aladin Akyurek]

yaaaaaaaaaaaay it worked

it took me some time to understand the combination between Sumifs , index formulas

thanks alot for your time ... u saved me

Great.

sorry but i need onemore thing to make it perfect formula


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

Can i define the bold blue range with address and match function ?

Try to set rather a max possible area, say C14:AZ200 and C14:AZ14 in consequence.

 

[EngMarine]

Great.

Try to set rather a max possible area, say C14:AZ200 and C14:AZ14 in consequence.

it work fine now

i've tried to make formula like it in anther workbook which also have 31 sheets

but it's quite different

i have table from E4 : AJ50 ( with header )
1 : 31 is located from M3 : AQ3
i want to sum the value which corresponding to certain text in column and certain text in header
i tried this array formula
=SUMPRODUCT(INDEX(INDIRECT("'"&$M$3:$AQ$3&"'!E4:AJ50"),MATCH(C11,INDIRECT("'"&$M$3:$AQ$3&"'!C4:C50"),0),MATCH(G9,INDIRECT("'"&$M$3:$AQ$3&"'!E4:AJ4"),0)))

but it gives me the value of 1st sheet only

what i have done wrong with it ?

 

[Aladin Akyurek]

Looks like...

=SUMPRODUCT(SUMIFS(INDEX(INDIRECT("'"&$M$3:$AQ$3&"'!E4:AJ50"),0,
  MATCH(G9,INDEX(INDIRECT("'"&$M$3:$AQ$3&"'!E4:AJ4"),1,0),0)),
  INDIRECT("'"&$M$6:$AQ$6&"'!E4:E50"),C11))

 

[EngMarine]

Looks like...

=SUMPRODUCT(SUMIFS(INDEX(INDIRECT("'"&$M$3:$AQ$3&"'!E4:AJ50"),0,
  MATCH(G9,INDEX(INDIRECT("'"&$M$3:$AQ$3&"'!E4:AJ4"),1,0),0)),
  INDIRECT("'"&$M$6:$AQ$6&"'!E4:E50"),C11))

it works great

sorry for late response i was out of my city for two days

can you explain to me how index function works with sumif ?

i know that sumif function require range , and index function gives single cell