Excel Tutorial | How to use SUMIF formula across multiple worksheets

In this lesson, we are going to learn how toy apply #SUMIF function across multiple worksheets with the help of #SUMPRODUCT function in Excel.
To download the exercise file used in this videp:
drive.google.com/uc?export=do...
► Buy Me a Coffee? Your support is much appreciated!
-------------------------------------------------------------------------------------
☕ Paypal: www.paypal.me/jiejenn/5
☕ Venmo: @Jie-Jenn
💸 Join Robinhood with my link and we'll both get a free stock: join.robinhood.com/jiej6
► Support my channel so I can continue making free contents
---------------------------------------------------------------------------------------------------------------
🌳 Becoming a Patreon supporter: / jiejenn
🛒 By shopping on Amazon → amzn.to/2JkGeMD
🗓 Get updated on new Python videos → / madeinpython
📘 More tutorial videos on my website → LearnDataAnalysis.org
📺 Also check out my 2nd channel Excel channel focus on sharing Excel tips: bit.ly/3B1DjSA
✉ Business Inquiring: [e-mail]
#SUMIF #Excel

[@petem7942]

Thanks for this. Extremely helpful and exactly what I was looking for. Very nice job of walking through the entire formula.

[@user-in5cj9cz3k]

Perfect!! This is the answer that I have been looking for... Thank you very much from Korea

[@nataraajank4072]

Exactly what I was looking for.... Thanks a lot for your help

[@luisamaya2513]

Worked a treat, solve my problems. Thank you.

[@AqsaOfficial]

Thank you so much u solved my biggest problem i am searching from 2 days

[@RubinGnoni]

Solved my long time problem, too. Thank you so much.

[@robbysandhu4425]

This is craziest formula for Pro levelers.. Thanks a ton

[@Elnord25pl]

Dude, you've just popped up right in time to solve my problem!

[@TinNguyen-bj2ze]

tks for so much, exactly what I need ,

[@hystericalgamer780]

Man that looks really complex but its the best guide ive seen for it, thanks so much

[@TheToddbart]

Very helpful sir!

[@jayRiles03]

Exactly what I needed. Thanks for your help!!

[@jiejenn]

Glad the video helped.

[@AdiNarayanaseelam]

Excellent formula , last and least i found

[@Farell722]

Thank you bro so much i appreciate when i learn new and i apply it during work and functions perfectly....

[@YuvaIL1]

You are a god!!!

[@PricOxD]

Hello, this video was excellent, it was the only one I found with exactly what I wanted! However, when I do it on my workbook which has tables on different sheets and the formula will only give me the correct value when the data is filtered in te other sheets under the indicated criteria. i.e I want it to sum a range of cells for the trader Jose, only when I have the data filtered under Jose in the other sheets I will get the correct sum, otherwise, excel will return pretty strange numbers. Would you know why would that be? I would highly appreciate your help.

[@phoeshane3952]

Thanks Guy..

[@janelliespina6458]

Just found this video and it was extremely helpful! I am hoping you can help me. I have multiple columns that I want to add using this formula, however, when copying the formula over the reference column stays the same. Is there a way it can change automatically when I copy over the formula to the adjacent column without having to manually change the cell reference? For example in cell C7 this is my formula: =SUMPRODUCT(SUMIFS(INDIRECT("'"&$A$2:$A$4&"'!B8:B50"),(INDIRECT("'"&$A$2:$A$4&"'!$A8:$A50")),$B7)). I want cell D7 to change to =SUMPRODUCT(SUMIFS(INDIRECT("'"&$A$2:$A$4&"'!C8:C50"),(INDIRECT("'"&$A$2:$A$4&"'!$A8:$A50")),$B7)) when i move it over. Is there a way to do this?

[@tolgaciik]

if we have 2 or 3 criteria can we do this with SUMIFS function? (ex. Red Apple Green Apple)

[@xlelliott7x]

Hey Jie thiss helped lots. Im wondering if you know how to also reference to another workbook aswell? so different workbook+different sheets. Im having trouble writing it.

[@jiejenn]

Unfortunately, I don't have an answer for you for this one.

[@xlelliott7x]

@@jiejenn Damn. all good, thanks for responding though.

[@anatomonster763]

when the sheet name to complex why i cant click the sheet insert the sheet nam into the formula

[@assuihermannkouame8708]

MAGNIFIQUE

[@Authentic_Alf]

Hi what if you have alot of sheets(64 sheets)? I can't name all tabs. What can I do to make excel sum from all sheets?

[@jiejenn]

If you have that many worksheets, I would probably write a simple VBA script to list all the worksheet names, then use that as my cell reference.

[@charlesbao6131]

Sorry but it wasnt clear for me, why the last digits we used the column D (from D2:D5)??

[@anastaysia]

Column D in his formula is referencing the revenue column on the region tabs. " If the cell in column A of the region tabs match the cell in column A on the Summary tab, sum all the value(rev) in column D of the region tabs." I hope this helps.

[@freyazsombory3122]

The text is blurry and I'm unable to see what you're typing in the formula bar. :(

[@mustaqimalfarabi8082]

Thanks, The pain in my head has left 😂

[@pyleoutnumbered8815]

Ugh, tried this 10 times and keep getting #REF and can't figure out what is wrong.

[@brettjaminieland8041]

Same here!

[@jennytaylia5371]

Hopefully you're still replying to comments.. Regarding using a reference sheet with the worksheet names rather than writing each worksheet in the formula, I've come across a problem. In the future, I intent to add more worksheets with simple names as needed (inc0001, inc0002, inc0003) so I made a refsheet and added inc0001 to inc0999 in column A from cell 1 to 999. I currently have two other worksheets to test with, inc0001 and inc0002. The problem is that when I use the shortcut you showed at the end, it only works so long as those worksheets already physically exist. Changing the formula to refsheet!$A$1:$A$999 resulted in #REF! but changing it to refsheet!$A$1:$A$2 works because those 2 cells say inc0001 and inc0002 which are worksheets that already exist. If I wrote the formula as refsheet!$A$1:$A$3, it would give me #REF! again because worksheet inc0003 doesn't exist (yet.) Is there a way to make it so that the formula will work without having to change it every time I add another worksheet, or will I just have to bite the bullet and make all the prenamed blank worksheets ahead of time?

[@jiejenn]

In your use case, I don't think there's easy way to do it. If I were you, I would probably look into using macros.

[@jennytaylia5371]

@@jiejenn thanks for the quick reply (really!) I was holding off looking up solutions just in case there were none out of the seemingly endless formulas and rules. I’ll start looking into what macros are 😅

[@vilasdalvi3426]

your thumbnail is sumifs and your showing sumif

[@jiejenn]

Ok.