Excel Magic Trick 1024: SUMIF & SUMIFS function & Mixed Cell References & Dynamic Table Range

Add with one or more conditions:

1. Learn how to add with one or more conditions using the SUMIF and SUMIFS functions.
2. Excel table feature to create dynamic ranges, ranges the expand as new records are added.
3. Learn how to use the Excel Table feature with or without the Table Formula Nomenclature.
4. See how to use regular cell references rather than Table Formula Nomenclature so that you can copy the formula to the side and not have the Table Formula Nomenclature move to different columns.
5. Learn how to create a cross tabulated table to add with two conditions
6. Learn how to use Mixed Cell References to help speed up formula creation time.

[excelisfun]

Just a guy having fun with Excel!

I am glad that you like the video!

[excelisfun]

That Table feature is annoying...
Glad you like the video!
Thanks for your support in buying the book!

[excelisfun]

Nice tip! EMT 1023 will be out in one or two days.

[lazyorbit]

i love how he talks about topics he's taught before like it's a brand new lesson.
not like other people who would just say, "hey i taught this before, go look at that other 20-minute video for this one trick"

[lazyorbit]

i never knew that the absolute cell references would be dynamic if you're referring to a table.

THIS VIDEO CHANGED MY LIFE.

[excelisfun]

Great tip about formulas across sheets!!

[excelisfun]

That is a hot tip! I will make a video to pass along your hot tip! Thanks!

[kay]

Thank you so much You have shared all data for studying, you are so generous !!!!
I am big fan of you.

[excelisfun]

If the table is on one sheet and the formula is on a different sheet, use the Table Formula Nomenclature, not normal cell references. This way the formula will be dynamic and update when new records are added.

[krn]

Cool trick. Never thought of selected range -1 to avoid the table nomenclature. Thanks Mike

[learntoearn]

Thank you very much.... After so many videos I found what I want.

[fernando]

We continue learning, thank you very much for your teachings

[msifre]

thanks for all your lessons. I learned to love Excel because of you and it has helped me so much in my professional life. Now I help other. Thanks

[gexcel]

Awesome, like always, i like particularly Ctr ', but pay attention mike, if the table are in another sheet, the normal references will not be updated in the summarize table, we have to use the nomenclature.

[zeeshankhawaja]

Nice Video BUT a tip for you:
you can lock the reference by typing "=SUM(Table1[[Date]:[Date]]" in column C and now you can copy it and paste it in Column D or F, it shall still refer to Table1[Date]

Have fun.

[nepalpankaj]

Hi Mike.. You can avoid the table reference from chaning when you copy the formula by copying and pasting instead of dragging the formula to populate other cells with formula

[elphau]

sounds like a solution to my current problem. THANK YOU!

[VinayShankam]

Thanks. I did post to Mr Excel forum and here is the solution/fix as FYI....operation on functions dont need to be in double quotes in SUMIFS "="&TODAY()-WEEKDAY(TODAY(), 15)+1)

[mysticrosemale]

Hey, i could not understand the changing of column into dog column. y it is changing. pls tell me.

[rozianarashid]

This is exactly what i am looking for. Tq for making my job more effective.