Again, brilliant demonstration! I like that you get right to it, rather than explain a lot beforehand like many others do. I also like that you show how the Indirect function works with both Sheet and Table references. Great job!
Hey Alan.. great video on technique using SUMIF and INDIRECT together. I re-created your sheets for practice and it worked like a charm. Also, did the same using SUMIFS which takes the arguments in a different order. An excellent tutorial and great tips. Thumbs up!
Hey! This vid was helpful to understand the basics, so thank you. I am trying to get a total for a collection of 'stores', that will then be accumulated together to make a 'region' total. Each 'region' has different 'stores' which do not duplicate. The current structure of the report is description of costs running in the first column and periods across the top - like a forecast sheet. Do you think a formula will be possible? I want to avoid using VBA if possible - many thanks!
Thanks Allan! You could even make a pivot like table with all the results. Actually referring and copying to Excel tables sometimes face difficulties, but not with INDIRECT. I will try this.
Hi, this is extremely helpful. I need a dynamic sum and criteria range, so that when I copy the formula over, the sum range and criteria range will change. Is there a way to do that in this example? It would reference a cell for the A:A and B:B.
Thanks! Absolutely. Yoh can combine an XLOOKUP to find the necessary sum and criteria ranges. I have a video on it on the channel. Search for XLOOKUP on the channel.
Thank you for this video. I have a lot of sheets and I want to sum the same category from each sheet in summary. So, how can I do or by this formula? Thanks
You're welcome. If the category column is the same, for example D:D, you could sum a range of sheets. Insert a sheet at beginning and end of all sheets as bookends, or place holders. Name the start and end. Use SUM('Start:End!'D:D)
Thank you. Sure. When a sheet name has spaces it is enclosed in single quotes. So for South Africa it would be 'South Africa'!D2 for example. So the INDIRECT would be as such INDIRECT("'"&A3&"'!D2")
Computergaga I will Edit " Region as Transactions Type Drop Down sale or purchase,category as items name & I will add 1 more Colom for date & then Total my model will ready for small shopkeepers.
Hi Alan, I'm trying to use this function, as it fits perfectly to my needs. However, I get a #REF!, and when i try to isolate the error step-by-step, it makes no sense. The indirect function finds the sheet, but fails to SUM. I have tried mimicking you work, but it's still failing. The only problem i can think of is either cell formatting or how the list (Yours have Toronto, Paris etc.) is created. Do you have any idea how to solve this? Great video by the way, easy to understand.
Sounds like the issue was with the space in the sheet name. When sheet names have spaces they must be enclosed in single quotes such as 'January 2019'!
I'm not sure what you're asking. You can sum that range simply with SUM(A1:D1) INDIRECT is used if you have a reference in a cell as text that needs converting to a reference. So, if you had A1:D1 written in cell F2, you could use SUM(INDIRECT(F2)) This is a strange scenario though.
Click in the range of cells that you want to make a table, click Home > Format as Table and confirm the range and then the name box is on the far left of the design tab.
maybe you could help me with a question regarding the indirect function: I have a document for keeping track of depreciations. Every fixed asset gets a sheet with its own depreciation table. On the first sheet of the workbook I want to sum up the depreciation over all objects for a given year. Because the number of objects is quite large I tried working with INDIRECT. The code looks like this: =SUMPRODUCT(SUMIF(INDIRECT(A1:A3&"!A:A");$F$1;INDIRECT(A1:A3&"!B:B"))) A1:A3 holds the names of the sheets in this case, in F1 you can enter the year you want to sum. Now the number of sheets isn't fixed. I want to expand the column INDIRECT is checking to - for example - A100 in both formulas and have a list of sheets generated per VBA macro in column A. But as soon as one empty cell is "referenced" I get a #REF error. I understand why, don't get me wrong, I just need to know if there is a way to prevent that and use my fomula without changing the matrix manually everytime. If there is a more elegant solution to my problem I am not seeing here, feel free to point me to them. Gladly appreciate that. Thanks in advance for reading through.
You explained it so clearly. Things that seemed so complicated looked so easy. Your tutorials are my go to if I am stuck. Thank You so much Alan.
My pleasure, Juanita. That is great to hear. Thank you.
Again, brilliant demonstration! I like that you get right to it, rather than explain a lot beforehand like many others do. I also like that you show how the Indirect function works with both Sheet and Table references. Great job!
Thank you for your kind words Joseph.
Hey Alan.. great video on technique using SUMIF and INDIRECT together. I re-created your sheets for practice and it worked like a charm. Also, did the same using SUMIFS which takes the arguments in a different order. An excellent tutorial and great tips. Thumbs up!
Thank you Wayne.
This will be very useful in case of payable and receivable accounts. Thanks for your efforts.
Thank you Lotfy.
Your videos are one of the best excel videos in youtube. Too good 👍
Thank you very much. Your comments are appreciated.
great way of explaining, thanks
You have no idea how long I’ve been trying to find how to do this😂 thank you!!
You're very welcome.
Hey! This vid was helpful to understand the basics, so thank you. I am trying to get a total for a collection of 'stores', that will then be accumulated together to make a 'region' total. Each 'region' has different 'stores' which do not duplicate. The current structure of the report is description of costs running in the first column and periods across the top - like a forecast sheet. Do you think a formula will be possible? I want to avoid using VBA if possible - many thanks!
Fantastic, thanks Allan
My pleasure Glenn.
Thank you. well explained.
You are welcome!
thank you for this. very useful
You're welcome.
Thanks Allan! You could even make a pivot like table with all the results. Actually referring and copying to Excel tables sometimes face difficulties, but not with INDIRECT. I will try this.
Thanks Bart. Good to hear from you buddy.
thanks allan, very useful , fantastic demonstration
Thank you.
Great and excellent as usual. Many thanks to you Alan. I like your videos so much.. 🌟 🌟 🌟 🌟
Thank you Salim. Much appreciated.
Very useful .. thanks
You're very welcome Murthy. Thank you.
Hi, this is extremely helpful. I need a dynamic sum and criteria range, so that when I copy the formula over, the sum range and criteria range will change. Is there a way to do that in this example? It would reference a cell for the A:A and B:B.
Thanks!
Absolutely. Yoh can combine an XLOOKUP to find the necessary sum and criteria ranges.
I have a video on it on the channel. Search for XLOOKUP on the channel.
Thank you for this helpful video.
You're very welcome, Carli 👍 Thank you
Awesome. Thanks.
You're welcome Salim. Thank you.
Thank you for this video. I have a lot of sheets and I want to sum the same category from each sheet
in summary. So, how can I do or by this formula? Thanks
You're welcome. If the category column is the same, for example D:D, you could sum a range of sheets.
Insert a sheet at beginning and end of all sheets as bookends, or place holders. Name the start and end.
Use SUM('Start:End!'D:D)
Very useful. Can you tell why the sheet reference does not work for sheet names with two words?
Thank you. Sure. When a sheet name has spaces it is enclosed in single quotes. So for South Africa it would be 'South Africa'!D2 for example.
So the INDIRECT would be as such INDIRECT("'"&A3&"'!D2")
@@Computergaga thanks a lot
This doesn't seem to work if there's a space in the tab name. Any thoughts on how to address that?
Nice.I can use it for Daily sale purchase Records.
Excellent.
Computergaga I will Edit " Region as Transactions Type Drop Down sale or purchase,category as items name & I will add 1 more Colom for date & then Total my model will ready for small shopkeepers.
Hi Alan, I'm trying to use this function, as it fits perfectly to my needs. However, I get a #REF!, and when i try to isolate the error step-by-step, it makes no sense. The indirect function finds the sheet, but fails to SUM. I have tried mimicking you work, but it's still failing. The only problem i can think of is either cell formatting or how the list (Yours have Toronto, Paris etc.) is created.
Do you have any idea how to solve this?
Great video by the way, easy to understand.
Well, i figured it out. My sheets we're called January 2019, February 2019. If i call them 2019January instead, it works.
Sounds like the issue was with the space in the sheet name. When sheet names have spaces they must be enclosed in single quotes such as 'January 2019'!
how do i do a sum range using the indirect function say from A1:D1 etc?
e.g. =INDIRECT(A1:D1) something like this
I'm not sure what you're asking. You can sum that range simply with SUM(A1:D1)
INDIRECT is used if you have a reference in a cell as text that needs converting to a reference. So, if you had A1:D1 written in cell F2, you could use SUM(INDIRECT(F2))
This is a strange scenario though.
please i need the sheet could do you share it
I don't have this sheet available anymore.
Super
Thanks Vinayak.
anyone know how to replicate this but with 3 criteria??? I add the third in the formula and it tell me that i have added too many arguments :(
Sure, you can use the SUMIFS function instead of SUMIF.
hi how to put a table name?
Click in the range of cells that you want to make a table, click Home > Format as Table and confirm the range and then the name box is on the far left of the design tab.
maybe you could help me with a question regarding the indirect function:
I have a document for keeping track of depreciations.
Every fixed asset gets a sheet with its own depreciation table. On the first sheet of the workbook I want to sum up the depreciation over all objects for a given year.
Because the number of objects is quite large I tried working with INDIRECT.
The code looks like this:
=SUMPRODUCT(SUMIF(INDIRECT(A1:A3&"!A:A");$F$1;INDIRECT(A1:A3&"!B:B")))
A1:A3 holds the names of the sheets in this case, in F1 you can enter the year you want to sum.
Now the number of sheets isn't fixed. I want to expand the column INDIRECT is checking to - for example - A100 in both formulas and have a list of sheets generated per VBA macro in column A. But as soon as one empty cell is "referenced" I get a #REF error. I understand why, don't get me wrong, I just need to know if there is a way to prevent that and use my fomula without changing the matrix manually everytime.
If there is a more elegant solution to my problem I am not seeing here, feel free to point me to them. Gladly appreciate that.
Thanks in advance for reading through.