SUMIF with INDIRECT Function for Dynamic Sheet and Table References
Вставка
- Опубліковано 12 вер 2024
- In this video, we look at using the SUMIF function with the INDIRECT function for dynamic worksheet and table references.
Learn it all with the Ultimate Excel Course - bit.ly/Ultimat...
Two amazing functions used together to sum values from a sheet specified as a cell value. We then use them to reference a table from cell value.
This technique can easily be adapted for any formula - VLOOKUP, COUNTIF, INDEX - the options are endless.
Find more great free tutorials at;
www.computerga...
** Online Excel Courses **
Excel VBA for Beginners ► bit.ly/2JvnnRv
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2t3netw
The Ultimate Excel Course - Learn Everything ► bit.ly/Ultimat...
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2viGg3J
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1
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.
great way of explaining, thanks
This will be very useful in case of payable and receivable accounts. Thanks for your efforts.
Thank you Lotfy.
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.
You have no idea how long I’ve been trying to find how to do this😂 thank you!!
You're very welcome.
Your videos are one of the best excel videos in youtube. Too good 👍
Thank you very much. Your comments are appreciated.
Great and excellent as usual. Many thanks to you Alan. I like your videos so much.. 🌟 🌟 🌟 🌟
Thank you Salim. Much appreciated.
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!
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.
thanks allan, very useful , fantastic demonstration
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)
Thank you. well explained.
You are welcome!
Fantastic, thanks Allan
My pleasure Glenn.
thank you for this. very useful
You're welcome.
Very useful .. thanks
You're very welcome Murthy. Thank you.
Thank you for this helpful video.
You're very welcome, Carli 👍 Thank you
Awesome. Thanks.
You're welcome Salim. Thank you.
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.