sum with multiple sheets

[shijilorotelu]

hi everyone, need little help here

i have 2 dropdwon list, each list represents the name of my sheets.
i want to sum value at the same cell of my sheets by choose it with my dropdownlist.

so, when i choose sheet #1 and sheet #3
it will sum value at cell A1 from sheet #1 , sheet #2 , and sheet #3

may you help me with the formula...

thank you.

 

[Special-K99]

Have a Google for Excel 3DSUM

 

[wideboydixon]

But the way you want to do it, you'll need to use a UDF; you can't use INDIRECT with a 3D sum.

WBD

 

[shijilorotelu]

But the way you want to do it, you'll need to use a UDF; you can't use INDIRECT with a 3D sum.

WBD

you mean VBA?

 

[markmzz]

hi everyone, need little help here

i have 2 dropdwon list, each list represents the name of my sheets.
i want to sum value at the same cell of my sheets by choose it with my dropdownlist.

so, when i choose sheet #1 and sheet #3
it will sum value at cell A1 from sheet #1 , sheet #2 , and sheet #3

may you help me with the formula...

thank you.

Hi!

Try this:

=SUMPRODUCT(SUMIF(INDIRECT("'"&INDEX(Lsheets,MATCH(B1,Lsheets,0)):
INDEX(Lsheets,MATCH(D1,Lsheets,0))&"'!A1"),"<"&9E+307))

Where Lsheets is your list of sheets names.

Markmzz

 

[markmzz]

Hi!

Try this:

=SUMPRODUCT(SUMIF(INDIRECT("'"&INDEX(Lsheets,MATCH(B1,Lsheets,0)):
INDEX(Lsheets,MATCH(D1,Lsheets,0))&"'!A1"),"<"&9E+307))

Where Lsheets is your list of sheets names.

Markmzz

Hi!

First, the formula below is a small modification in Aladin's formula.

Now, you can try too this small modification in T.Valko's formula:

https://www.mrexcel.com/forum/excel...-syntax-referring-range-two-worksheets-2.html

=SUMPRODUCT(SUMIF(INDIRECT("'"&ROW(INDIRECT(B1&":"&D1))&"'!A1"),"<9E307"))

Markmzz

 

[shijilorotelu]

Hi!

First, the formula below is a small modification in Aladin's formula.

Now, you can try too this small modification in T.Valko's formula:

https://www.mrexcel.com/forum/excel...-syntax-referring-range-two-worksheets-2.html

=SUMPRODUCT(SUMIF(INDIRECT("'"&ROW(INDIRECT(B1&":"&D1))&"'!A1"),"<9E307"))

Markmzz

it's works man...thank you

btw, what is "<9E307" means?

 

[Special-K99]

less than 9 followed by 307 zeroes.

Looks like it's the highest number a cell can hold according to another website.

 

[markmzz]

less than 9 followed by 307 zeroes.

Looks like it's the highest number a cell can hold according to another website.

Thanks Special-K99 for help the user.​

it's works man...thank you

btw, what is "<9E307" means?

Hi Shijilorotelu!

The <9E307 is used in the formula to catch all numbers (less than 9E307 - a big number like Special-K99 said). In your case, to catch all numbers in cell A1 of the sheets 1, 2 and 3.

By the way, the highest allowed positive number in Excel is 9,99999999999999E+307 (look at the link below).

https://support.office.com/en-us/ar...67e-8e27-269d656771c3?ui=en-US&rs=en-US&ad=US

I hope that this helps.

Markmzz

 

[shijilorotelu]

less than 9 followed by 307 zeroes.

Looks like it's the highest number a cell can hold according to another website.

Thanks Special-K99 for help the user.​

Hi Shijilorotelu!

The <9E307 is used in the formula to catch all numbers (less than 9E307 - a big number like Special-K99 said). In your case, to catch all numbers in cell A1 of the sheets 1, 2 and 3.

By the way, the highest allowed positive number in Excel is 9,99999999999999E+307 (look at the link below).

https://support.office.com/en-us/ar...67e-8e27-269d656771c3?ui=en-US&rs=en-US&ad=US

I hope that this helps.

Markmzz

thank you for your help man \m/