I wish i saw this before my interview

It helps

Thanks alot .. you save my life ❤

Thank you sooo much I got the solution of my error....... Very use full

I must not be applying it correctly. When I use SUM on a list of money (6 lines consisting of $245.00, $559.98, $150.68, $35.50, $0.22, $0.23) I get $991.61. When I use Subtotal Function_num is 6, Ref1 is (in my spreadsheet W148:W153 which holds the cash values listed above) I get $37,134,115.34. I know the subtotal of those figures is no where close to $37 million. What am I doing wrong? I'm not using a table, it's just a simple Price list of materials. Shouldn't in this case, the Sum value and the Subtotal value be equal? Edit: I'm not using any filters, much the same as in your first example.

One limitation that VLOOKUP (and HLOOKUP) have is that because you specify the return column _as a parameter_ in the function (unlike LOOKUP where it's a _reference_ to a cell range), if you have a situation with multiple vlookups running in parallel (for example, instead of just picking a desired value from your example table you instead want to return the full row), you cannot simply write the formula once (with a mix of absolute/relative referencing) then copy/paste it as needed.

Nice fix for my REF error. Thanks for explaining

AGREGATE Is a superior function, not only has the same functionality of SUBTOTAL but also has options to ignore error cells

Here's another great tip When producing an informative clip try to make sure that the music (if it absolutely must be included) is at a lower volume than the narration. Its referred to as 'background music' for a very good reason.

I didn't realize that about Subtotal. I just always used Aggregate for the same reasons you mentioned. I guess that Aggregate is for larger sets that might include Subtotal since it can ignore Subtotal but probably not vice-versa.

use "add table" to create a table with automatic formatted header colums. if you the add a sum it wiil automatically use subtotal... no magic involved.

The most useful aspect of SUBTOTAL I use is that any SUBTOTALs in the range are ignored (unlike SUM). So using SUBTOTAL(9,... within a column allows for subtotals which are ignored when the whole range is SUBTOTALled, eg: In column C having values, with =SUBTOTAL(9,C1:C5) in C6 and =SUBTOTAL(9,C8:C10) in C11, and =SUBTOTAL(9,C1:C12) in C13 the first two SUBTOTALs will sum their respective sections (C1:C5 and C8:C10), and the final SUBTOTAL sums just the values, ie the equivalent of SUM(C1:C5,C8:C10) but easier to insert extra ranges with subtotals if necessary as it doesn't need to be changed.

No thanks, I'll keep using SUM. Rarely have I ever come across a case where I wanted a cell value to _change_ based on whether rows are hidden. If i want a sum based on a subset of data, I will use a SUMIFS or an array function.

Well, horses for courses applies here. The SUM() function remains useful if you have information being relayed to another tab which you don't want to be affected by filters active in the current tab. Although SUBTOTAL() has many useful everyday applications, it isn't a complete replacement for SUM().

Been using aggregate for ages..... Imho better than subtotal 😉

Where is your notation of using a fullstop (.) as thousands separator, being an accepted standard? It appears very weird to me. I prefer a space as the separator.

helpful info! thank you

Great video and direct to the point. Very informative. Thank you.

SUM is an older function probably created much earlier when filters where not a thing hence why it works that way hence why it's not filter aware.

when looking at the formula to debug it... sum works better, as you can see it. Subtotal works better WHEN you are using filtering of some sort... that makes sense. But that is not what your title is. IMO sum is easier to use, when I am not dealing with a filtering list.

Different functions for different purposes

tried it - but cant get it to work....:(

Ai, Vlookup explaing while we have Xlookup ? why, Vlookup is redundant now

Which solution fixed your error? Let me know below!

Just what I needed and also nice and concise!

I never knew this, so I have learnt something new today, but I will forget it tomorrow, old age. However, =sum is great if you are doing certain stuff. Yes, if you are doing something like this, then subtotal would be a lot better. You are only going to use this, if you are a professional and doing stuff like this,

This is really useful. This has been a problem for me so many times!

They areth usable functions and can be used quite successfully concurrently. Sum, is to display the total value, Subtotal to display the sum of filtered items. So easily you can compare the filtered valuea from total values using same table range.

Thank you for that tidbit. I forgot about SUBTOTAL and its multitude of variations (Sum, Min, Max, etc.). What else have you got?

Pfff, just stop using excel.

So, the answer is that if you just want a total sum is good enough but if you intend to use filters you may want to use subtotal along with or in place of sum.

very nice. Thanks!

Subscribed. All. Thanks.

Music way too loud

Thanks. Wouldn't the AGGREGATE() function not be even more versatile?

1.215 ? What kind of sorcery uses a decimal place instead of a comma? ;)

Understanding both and using both is fine. Problem is when folks do NOT understand and expect SUM to work with filters the same way as SUBTOTAL (which would make for redundant functions). Lesson - Expand understanding of functions like SUBTOTAL to add to a toolbox carrying SUM ;-)

This is great, and solves an issue I had been wondering about. Thanks

I use FILTERS (shortcut: Ctrl-Shift-L) on EVERY spreadsheet, so these--SUBTOTAL and AGGREGATE (mentioned by a commenter)--are tremendously useful tips!

Had this literal issue last week. Filtered a table, had sum give me bad data. Changed to subtotal and it works just like this video says.

Use Aggregate instead of Subtotal.

Some function is not ideal.

Great info, but why do you need music blasting in the background? It's so distracting!

Stop using SUBTOTAL, Switch to AGREGATE

I always use Alt + = inside the table and select sum from drop down

Why are you deleting my comments?

Thank you for sharing this piece of advice. The aggregate function is even better (managing errors, hidden lines and much more)

Use Aggregate, not Subtotal.

Hi, Very well presented and to the point - often we forget the simple or primer tutorials eg why text is justified different to numbers, - a few time i cant understand why data is not right and find someone keyed in a number as text ( or copied it from say a word or pdf as it is text formated ) and then justified the cell to make it look correct I knew sum totaled the area that was highlighted by the "drawn box" Also that filters only hide items and display what you are searching for Because of that i use SUMIF - but that only really works good for a single or few argumetns eg a mixed sales report, you use the month or year funtion to break downa date into a category, and then use sum if for jan , then feb ( i use this daily at work plus the other version of the sales source eg rep id or counter sale - we have reps and separate sales offices ) However for large data i use pivot tables - they do the same and show subtotals corectly