🔥WATCH NEXT: Another Powerful Excel Formula: kzread.info/dash/bejne/gWlqzquzns2_lMo.html

@braco0000

2 жыл бұрын

Hello Tiger... What does "DSUM" stand for?

CTRL + SHIFT + L to enable filters, you don’t need to select the whole range either

@sportssalam

3 жыл бұрын

Yes, usually i am go on that method

@aynino

3 жыл бұрын

Yes but if for some reason there are empty rows in your table, the data after won’t be taken into the filters if you don’t select your entire table first.

@KingsoTV

3 жыл бұрын

​@@aynino agreed, if you are using the filtering to cleanse the data

@TigerSpreadsheetSolutions

3 жыл бұрын

I like it!

@g.j

2 жыл бұрын

What about the shortcut key to remove the table?

Ctrl Shift L enables filters in all columns without having to select the column. Alt ⬇️ drops the filter menu, pressing E after that takes you directly to the search box. Not exactly a replacement though, like with all analytical issues, it depends on the scenario you're testing.

@TigerSpreadsheetSolutions

3 жыл бұрын

Absolutely

@johnmathew9211

3 жыл бұрын

Thx

@RR-ho3td

3 жыл бұрын

Or just click on one of the headers then enable the filter?

HA! I was expecting something 'new' but this is a blast from the past....I'm happy to see your audience so excited. I've been using DSUM, etc. since before Excel had filters. Before Pivot Tables were in Excel, I used to build pivot tables from 100,000 lines of data using five or six criteria DSUM, DGET, DCOUNT, DMIN, etc.

@TigerSpreadsheetSolutions

3 жыл бұрын

Happy Mikey likes it too! Welcome ...

@nigeltufnel4031

2 жыл бұрын

Excel 2.0

I can’t wait to checkout your Excel Cheat sheet. I just found your channel and subscribed. I need to become proficient at work with Excel. Thank you this will be so helpful!

@TigerSpreadsheetSolutions

3 жыл бұрын

Welcome to the community!

Wow! That was a great help. Thank you so much, man! You saved a lot of my time

@TigerSpreadsheetSolutions

3 жыл бұрын

Great to hear Abdul - nice job!

Thank you for this instructive presentation. I plan to experiment with DSUM instead of filters.

So, are you giving up on filters? Let me know in the comments, I will get back to you personally ...

@craigwhitthread3050

3 жыл бұрын

Not a fan of filters myself. In Excel 365, I use the new Filter and Sort formulas :-)

@TigerSpreadsheetSolutions

3 жыл бұрын

Very nice ... I will be exploring their potential in the coming months and years

@SpiderHamPeterPorker

3 жыл бұрын

@@craigwhitthread3050 Unfortunately, Excel's filter function is not nearly as good as Google Sheets, yet. But that's not the point of this video. He's not looking to just prepare a filtered table. He's just running a filter to sum a filtered column and replace that task with dsum here and it can also be done with sumproduct, sumifs and other functions. Still, I learned a cleaner way to do it with dsum. Nice, and thanks OP.

@JJ_TheGreat

3 жыл бұрын

@@SpiderHamPeterPorker Agreed, it is such an inefficient use of filters just to get a sum. My point though is that filters are still useful. Sometimes you want to not only just add the data up, but you want to actually show someone the actual data - which includes only the criteria which you are looking at - and that is when filters are still useful. Sometimes I filter the data, and then I copy the filtered list and paste/paste values into a different workbook.

@harshanaweragama270

3 жыл бұрын

Nope. I will still use the filters, as in my situation, i used filters not to sum. But i did learn a new function to do the summing.

Nice explanation, but i am still prefering sumifs, even if with harder syntax. Because i normal want to results for more variants, problem of sdum is the header of criteria, you have to have criteria value directly below the header, you cant tou use one criteria table with more rows... For more variants on one shot you have to use every time new criteria table for each dsum (you cant to have only table of criteria variants)

@MatthewMarsett

2 жыл бұрын

You should check out using the SUMPRODUCT function for trying to sum when there are numerous criteria. I used to use sumif quite a bit but eventually my "criteria" ran into 5+ "ifs" and SUMPRODUCT just became easier to write.

After 'Alt+Down Arrow' key, if you press 'E', you reach directly to the search box instead of going down using down arrow key.

@TigerSpreadsheetSolutions

3 жыл бұрын

I love this one ❤

@nomanraihan5477

3 жыл бұрын

Thank you!

I just learned some amazing shortcuts and tricks that I know I will use often. Well earned subscribe my man. Thanks!

@TigerSpreadsheetSolutions

2 жыл бұрын

thanks Travis and welcome to the channel!

Interesting. I've used DSUM a few times but never really been too keen with how the criteria are defined and prefer the more interactive method of getting the filter output to match the results cells. I've tended to use =SUBTOTAL(... ,9) to add up what's visible at the time, or alternatively use array formulae in the style of {=SUM(basevalues*(filtervalues=filtercondition))}, where anything that matches produces a multiplicand of 1 and anything that doesn't, a multiplicand of 0, so only basevalues in line with a matching condition on the same row get multipled by 1 and added.

@TigerSpreadsheetSolutions

Жыл бұрын

Hi Mark - thanks for the ideas. My objection to subtotal is that you have to go through the filter menu to make it work. That's time-consuming ...

Love this formula! It's like a simplified form of the SUMIFS formula 😊

@TigerSpreadsheetSolutions

2 жыл бұрын

I couldn't agree more - thanks!

I find Alt, A, T is bit of a handful to press sequentially. Someone showed me Ctrl+Shift+L to do the same thing, no need to get the sequence right, just mash the keys at the same time and it's toggled the filter. Also press E once you're in the filter box to get the cursor in the filter text box straight away.

@TigerSpreadsheetSolutions

3 жыл бұрын

Solid tips!

@smalldjo

2 жыл бұрын

that last tip about pressing 'E' could save lives ! ty

I love stumbling across excel cheat sheets, thank you :)

@TigerSpreadsheetSolutions

3 жыл бұрын

Welcome to the channel, Bryan!

Another great prospective, thanks a lot for sharing..enjoy your presentations.

@TigerSpreadsheetSolutions

2 жыл бұрын

You're welcome, Pete! Thanks and do check out the other videos on the channel.

I'm giving up on filters. Cool instructions.

@TigerSpreadsheetSolutions

3 жыл бұрын

Welcome to the channel!

When you first started, I was like this is stupid..I love filters. But at the end I ate my words. Great video.

@TigerSpreadsheetSolutions

3 жыл бұрын

Many thanks, James! Do check out the other videos ...

@NewYears1978

3 жыл бұрын

@@TigerSpreadsheetSolutions I plan to watch them all, they are a bit long winded for me but they are awesome! Glad I found you!

@TigerSpreadsheetSolutions

3 жыл бұрын

James Pyle x2 speed? Good luck!

@NewYears1978

3 жыл бұрын

@@TigerSpreadsheetSolutions Haha maybe so!

I really appreciate your video training which is simple and comprehensible.

@TigerSpreadsheetSolutions

Жыл бұрын

Thank you my friend - do check out the other videos!

Appreciate your patience in explaination. Made it easy.

@TigerSpreadsheetSolutions

3 жыл бұрын

Thank you and welcome to the channel!

I’m a kid in a candy store. Keep sharing these please!

@TigerSpreadsheetSolutions

3 жыл бұрын

Welcome, Leonardo!

@tylerjordan1089

3 жыл бұрын

I second this.. if only I could find a video simplified as this to have my order guide spreadsheet automatically update the prices of products at the same time as my venders site online does. For example, I order all food for my restaurant from Reinhart food service. Which is online and I have to log into my account to place orders and or get current prices on every ingredient I have in my inventory. Making me have to update every item manually which with over 500 items is VERY time consuming. However it is worth it seeing soon as I update all my costs per ingredient/item I have it set to automatically update what my cost is per menu item/recipe as well as what my food cost is based on my menu pricing based off of whatever I decide to have my markup set as.

Chris, first time viewer, excellent video. Thank you

@TigerSpreadsheetSolutions

3 жыл бұрын

Thank you and welcome

"It's so tiring, it takes so long, so many mouse clicks" :) then takes no mouse clicks to set all filtering data. in 1 minute and 3 seconds. Then you need, to add fields in cells, setup the formula, be aware that everything is exactly correct. in 5 minutes (without the exra explaination it woudl still be longer and more effort). Yes DSUM is powerfull, but dont say its a replacement for faster way of using filters. The one has nothing to do with the other.

@TigerSpreadsheetSolutions

2 жыл бұрын

It's a replacement for using filters to sum filtered rows, as I explain in the video. Thanks for the comment!

Just discovered this video / channel. Awesome stuff, thanks!! I might reflect on this and change my view, but my first thought is that VSUM is very similar to Sumifs or an array formula, where you build the search criteria into cells that the formula references to, rather than into the formula itself. Cool stuff though, and I’ll definitely give it a try.

@TigerSpreadsheetSolutions

3 жыл бұрын

Hi Iain - yes, it's certainly similar. But, there are many ways to perform this function in Excel. DSUM is my preferred way because of the conciseness of the formula. Others may agree or disagree, try to form your own view over time - good luck!

Never knew this formula before, great tool, thanks.

@TigerSpreadsheetSolutions

3 жыл бұрын

I hope you get value out of it ...

DSUM can be really useful... didnt knew about this formula before, thanks for the explanation.

@TigerSpreadsheetSolutions

3 жыл бұрын

You're welcome Faiz - I hope it helps you!

Nice video. I liked and subscribed to get more of these in my recommendation

@TigerSpreadsheetSolutions

3 жыл бұрын

thank you and welcome to the channel!

Just found your channel. Excellent tutorial! Thanks for sharing this Gem! Subbed.

@TigerSpreadsheetSolutions

2 жыл бұрын

Welcome, my friend!

As an Access & Excel Developer, it's interesting to see the DSum() function in Excel. However, I'm curious as to why use it when a SumIF() or SumIFS() can accomplish the same thing?

@TigerSpreadsheetSolutions

2 жыл бұрын

It depends on your mindset. If your mindset is: 'I have a tool to do that, I don't need another one' You might not get value. If you mindset is: 'I need a range of tools so I can pick the best for the job' You'll get value, even if you know SUMIFS My experience has driven me towards mindset 2. In terms of direct comparison, I prefer the conciseness of DSUM vs. SUMIFS.

@sandornyemcsok4168

2 жыл бұрын

The only advantage of DSUM what I can see is that it specifies the criteria header by the content of it thus searches for it automatically. If the column order of the database would change then the formula does not have to be updated, while in case of SUMIF if a column would be placed elsewhere next time then you have to update the formula. It is quite minor advantage for me though as database column order changes are quite rare in my practice, thus I also stick to SUMIF :)

I love creating userforms and using VBA. I am going to start saving my favorite formulas and this one is pretty cool!!! Thanks a bunch!!

@TigerSpreadsheetSolutions

3 жыл бұрын

You're very welcome MIke - we cover lots of cool formulae on the channel, enjoy!

@nigeltufnel4031

2 жыл бұрын

VBA is almost never needed. The worksheet is more powerful than most people understand

@michaelthomashill

2 жыл бұрын

@@nigeltufnel4031 - It has not failed me yet. I am learning typescript too and I love Python...wait, I shouldn't enjoy it ? I am confused by your lack of contribution to my post.

@nigeltufnel4031

2 жыл бұрын

@@michaelthomashill never said you shouldn't enjoy it. Have fun

Excel-lent short tutorial! I love Excel!

@TigerSpreadsheetSolutions

3 жыл бұрын

👏👏👏

Many thanks - another piece of learning for me !

@TigerSpreadsheetSolutions

2 жыл бұрын

Good to hear, Rob!

Really useful and some great tips, thanks! I usually use sumif(s) and reference criteria cells so may try a comparison.

@TigerSpreadsheetSolutions

2 жыл бұрын

Thanks John - I will put a comparison video together someday, given that many viewers are comparing to SUMIFS. Thanks for the comment!

Awesome video bro!!!

@TigerSpreadsheetSolutions

3 жыл бұрын

Welcome to the channel my friend!

Incredible technique!!! Thanks alot Sir for sharing this :)

@TigerSpreadsheetSolutions

3 жыл бұрын

Welcome, Hassan!

REALLY AMAZING channel, why I find it so late... Thank you.

@TigerSpreadsheetSolutions

3 жыл бұрын

Welcome - do you check out the other videos!

@beginho2454

3 жыл бұрын

@@TigerSpreadsheetSolutions , sure, i need more time, checking one by one... really useful!!!! you are so great!!!!! helps me a lot. you are the excel guru!

Very helpful, thank u so much.

@TigerSpreadsheetSolutions

3 жыл бұрын

Thank you and welcome to the channel!

Whoa....I have to let that soak in a for a minute. I have done similar in the past, but it has been a complicated group of "SumIf"s. This looks much better!

@TigerSpreadsheetSolutions

3 жыл бұрын

Great to hear, Dan!

Thanks!!! It is very useful!

@TigerSpreadsheetSolutions

3 жыл бұрын

Welcome to the channel!

Wow! That's great. Thanks as always.

@TigerSpreadsheetSolutions

2 жыл бұрын

Thanks Peter! Do check our other videos ...

Never heard of this. I use a fancy index match array formula to live filter. You can do what your doing with sumifs though.

@TigerSpreadsheetSolutions

3 жыл бұрын

True, but not as elegantly as with DSUM

What a great video! Thanks for sharing!

@TigerSpreadsheetSolutions

3 жыл бұрын

Thanks Frank and welcome!

More than one way to skin a cat. This seems pretty nifty and would become more useful, the greater the number of different fields in your criteria. If you wanted to see all teams side by side, you could create a "helper" column to test if the 1.54 was true and then insert a pivot table to sum up the desired value by team where the helper column is True. Super quick to setup as well.

@TigerSpreadsheetSolutions

3 жыл бұрын

If you want to use pivot tables that is ... thanks for the comment!

You're doing the long way....just right click on the data and filter by selected cell, it auto adds the filters and all....cheers mate

@TigerSpreadsheetSolutions

3 жыл бұрын

Thanks ... you did watch the rest of the video, right? :-)

Thank you for the video Mr. Chris

@TigerSpreadsheetSolutions

Жыл бұрын

Thank you, my friend!

For show / remove filter, go to first heading and type ALT, D, F,F all one after another, not together. Many ways to do one thing. When you changed the name and the total changed looked like magic. Need to understand how is it doing calculation and is the order of the content of small 4 cell table important. Very powerful formula. Thank you

@TigerSpreadsheetSolutions

2 жыл бұрын

Thanks Sujal - correctly set up, it does feel like magic. I hope you enjoy this one!

This looks perfect for my database, got a few ideas how to expand on it. Thank you. Will look to re-sub again when time allows!

@TigerSpreadsheetSolutions

3 жыл бұрын

Great to hear, Craig!

I'm this situation I typically use a sumifs formula with reference ("*"&cell&"*") to text contained from my desired cells. This gives me a lot of flexibility in how i use the drag features to build a reference table. For example I can pivot out a complicated patient interview, then sum or count mentions of topics that I'm looking for depending on the variables. This seems like a similar solution? Can you go over how this formula differs from sumifs?

@TigerSpreadsheetSolutions

3 жыл бұрын

It's similar but I prefer the shorter formula for one thing. I will do a video on the differences one day ...

This is amazing.

@TigerSpreadsheetSolutions

3 жыл бұрын

I agree!

I'd normally use pivot table or subtotal. definitely will try dsum next

@TigerSpreadsheetSolutions

3 жыл бұрын

Highly recommended...

@joejj6251

3 жыл бұрын

Also, countifs is pretty similar.

@TigerSpreadsheetSolutions

3 жыл бұрын

@@joejj6251 You can use DCOUNT for counting, just like DSUM

Loved it.....👏👏👏🙏👍

@TigerSpreadsheetSolutions

3 жыл бұрын

Thanks for watching 🙏

Nice video. Thank you :)

@TigerSpreadsheetSolutions

3 жыл бұрын

It's a pleasure Rejhan and welcome ...

CTRL+SHIFT+L to filter and you can be anywhere in the data set ALT+DOWN then E to search, and with dates you can go into the year, hit RIGHT to expand..

@TigerSpreadsheetSolutions

3 жыл бұрын

Nice ideas, thanks

Great Video.. Thank-you. However you should note that the column that you wish to see the Sum (i.e."J" in your example) must be formatted as "Number" else your results will be in error. :)

Very nice Sir. Thanks a lot

@TigerSpreadsheetSolutions

3 жыл бұрын

Welcome to the channel Prakash

Chris I have been using VBA for years, written some complex automations and everytime I click one of your videos I learn something new. This is great stuff, thank you

@TigerSpreadsheetSolutions

3 жыл бұрын

Great to hear Christiaan!

Nice solution will use it. One observation you were labouring away at filter set up. In windows PC, assuming you have a proper data set, shortcuts ctrl + shift + l (L not case sensitive) for filter handles on the top row or ctrl + t for conversion to a table with automatic filter handles. I know the point is to not filter but some time you need them. 👍

@TigerSpreadsheetSolutions

3 жыл бұрын

'laboured' = best adjective for describing my videos :-) thanks for the tip but I generally avoid Excel tables

good job!

This is really amazing😳

@TigerSpreadsheetSolutions

3 жыл бұрын

Thanks Sherwin and welcome

Nice one. Great tool that is. Thank you

@TigerSpreadsheetSolutions

3 жыл бұрын

He lives! Thanks Rik and welcome to the channel.

Thank you sir. Please explain how to use standard formula for totalling date-wise debit and credit columns and balance in next line, like cash book in tally.

Because the only time you use filters is when you want to sum certain rows 🤔 (and in those cases you could use SUMIF).

@TigerSpreadsheetSolutions

3 жыл бұрын

Personally I find DSUM much easier to put together. Thanks for comment!

nice tutorial. good job. thanks

@TigerSpreadsheetSolutions

3 жыл бұрын

Thanks Ade, and welcome

Thanks, that made sense

@TigerSpreadsheetSolutions

Жыл бұрын

You're welcome - please check out our other videos

For small jobs, Filter. For large jobs, VBA. For a time-killer while waiting patiently for a phone call, or an email, or a pizza delivery, DSUM. Makes sense to me. But that's just me. And I'm the guy that has to work with me, every day.

@benoitkinziki3916

3 жыл бұрын

Why do use VBA for large jobs? I thought it was less efficient than excel formulas

@TigerSpreadsheetSolutions

3 жыл бұрын

I would say VBA and formulae do different things - formulae for data analysis and modelling, and VBA for automating processes

Amazing! Will come again.

@TigerSpreadsheetSolutions

2 жыл бұрын

See you again soon, Paul!

Thank You!

@TigerSpreadsheetSolutions

3 жыл бұрын

You're welcome!

I can see the power of this but the problem showed could have been solved by a sumifs statement. The question I have which is better in terms of processing speed. I know that the columns being interrogated can be changed easily but you can do this with dynamic ranges and use of indirect. I can see this being really beneficial in VBA though

@TigerSpreadsheetSolutions

3 жыл бұрын

Great question - I do plan a follow-up video, I would like to know about efficiency vs. SUMIFS too. Watch this space!

This could be handy. Is there a way to apply multiple values for a given criteria? Also, if you format your dataset and criteria as a table, you can reference something like this: =DSUM(tblResultsData[#All],tblResultsData[[#Headers],[Returns Home Win]],tblFilterCriteria[#All]) This reference style makes the formula a bit more dynamic and less error prone, as you can leverage the auto-complete functionality that comes with tables.

@TigerSpreadsheetSolutions

3 жыл бұрын

Jason - the Microsoft resources say that 'OR' logic is possible with DSUM by adding more rows to the criteria table, and adjusting the criteria range to include them. So, you wouldn't need a repeated column header to do that, in theory. I have never got this working consistently, however. Great that it also works with the table notation though I personally try to avoid tables. I use INDIRECT or similar to dynamically define the data range.

@jasongardner8302

3 жыл бұрын

@@TigerSpreadsheetSolutions Yes, that does work. It is a little nuanced though in that, if one of the rows in the criteria table is null, it will sum the entire table's results. I suspect there's a logical reason in the documentation, I'll need to take a look. But, the formula does work and the criteria auto expands with the table, which is nice. You just have to be mindful not to have a blank row. Out of curiosity, why do you avoid tables?

In my workbook I've been working on I use it to double as an inventory guide as well as an order guide. To get total $ on hand broken down between liquor, beer, and soda I use very similar formula which is a sumif. It is =SUMIF(Sheet1!$k$4:$k363,"beer",Sheet1!$J$4:$j$363) then a cell for liquor and Pepsi as well. Can you tell me the difference between sumif and dsum?

@TigerSpreadsheetSolutions

3 жыл бұрын

Hi Tyler - DSUM does the same thing with a shorter formula

Not bad, I'll probably stick to Ctrl+Shft+L, but I'm gonna subscribe to the channel because you remind me of David Tennant

@TigerSpreadsheetSolutions

3 жыл бұрын

😂😂😂Welcome!

@eddyr3691

3 жыл бұрын

I notice the difference is the cell in which excel leaves you. Using Ctrl + Shift + L you stay on your original cell. Whereas Ctrl* puts you in col1row1 of the selected range.

Thanks, i have been using sumifs for years in which suits perfectly fine until now.

@TigerSpreadsheetSolutions

3 жыл бұрын

Great to hear - for me, DSUM is different and easier to use

Crtl+A is a great alternative shortcut to the ctrl+shift+right then ctrl+shift+down that you were using to select the data you want filtered. Also, you don’t have to have the top left most cell selected, it could be any cell within the dataset. Another great alternative is ctrl+shift+L to enable filters vs the alt+A then T. This one is minor but it’s a more direct command. You can actually see the difference in speed if you compare the two.

@TigerSpreadsheetSolutions

3 жыл бұрын

nice shortcuts!

Thank you!

@TigerSpreadsheetSolutions

2 жыл бұрын

You're welcome!

First of all I wish to thank you. This example shows a standard "AND" condition filter. But there's also the advanced filter which allow us combining "AND" and "OR" conditions. Is there any function like DSUM in this situation? Or maby the SUMIFS function is the only way? Thanks

@TigerSpreadsheetSolutions

3 жыл бұрын

I have read that DSUM allows OR operations - but I have never got this working personally ...

Nice work

@TigerSpreadsheetSolutions

2 жыл бұрын

Thanks Rajesh and welcome to the channel

So, how different from SUMIFS is this beside that it looks easier to setup for the criterias.

@TigerSpreadsheetSolutions

3 жыл бұрын

Easier setup is the main advantage for me

Well, I used to use DSUM a lot in the 90's, but then when SUMIFs came up, I realized it was much more simplified with the SUMIF's. You can still use the variables as criteria like this: ">"&1.54

@mesa8615

2 жыл бұрын

This is the simplest formula. I've it. This is the best than dsum.

@josemarquez133

Жыл бұрын

make sure it's ">1.54"

I use Numbers’ Categories in Mac/iPad, instead of Excel, for files the heavily use Filter. Much much better.

@TigerSpreadsheetSolutions

3 жыл бұрын

What aspects are better in your view?

@Tiger Spreadsheet Solutions - Thanks for your video! How would you approach using DSUM to sum all but beagles and dachshunds as home breeds? How would you approach using it to sum over only those two breeds? I'm assuming you would have multiple instances of the "Home" column in the criteria range, but thought there may be some way to combine them.

@TigerSpreadsheetSolutions

3 жыл бұрын

Michael - the Microsoft resources say that 'OR' logic is possible with DSUM by adding more rows to the criteria table, and adjusting the criteria range to include them. So, you wouldn't need a repeated column header to do that, in theory. I have never got this working consistently, however. You can use repeated headers for 'AND' logic. You can something like "Text" to exclude certain values. Good luck with it!

@nigeltufnel4031

2 жыл бұрын

Array formulas or pivot table

That’s great. How would you use wildcards in the criteria?

@TigerSpreadsheetSolutions

3 жыл бұрын

Thank you - various options are possible, check out Microsoft's learning resources on this formula

Thanks 👍👍

@TigerSpreadsheetSolutions

2 жыл бұрын

You're welcome - do check out the other videos on the channel

Hi, Chris! I was wondering if there's a way to automate an excel file that will allow me to ploy training classes for a bpo company?

@TigerSpreadsheetSolutions

3 жыл бұрын

Hi John - with VBA the only limit really is your own creativity

Pretty nifty for What If scenarios. It's a pity that the criteria box trick doesn't work for formulae like filter and sort.

@TigerSpreadsheetSolutions

Жыл бұрын

I'm not sure Mike - perhaps you could develop this mechanism to something more sophisticated, and incorporate the above functions ...?

Very convinient )))

@TigerSpreadsheetSolutions

3 жыл бұрын

Thanks, Jasper!

kinda like power pivot, sumx nested with filter, like it, thanks.

@TigerSpreadsheetSolutions

3 жыл бұрын

I love this one ...

I need some help. Have an excel sheet that my department log important information on and someone applied a filter looking for a particular piece of information, but the filter that they applied rearranged the entire sheet and messed up the order of the information imputed. Can someone tell me if it's possible to undo the filter even though the person saved and closed off the document and can you advise me as to how I can get back the original order that my document was in? Thanks.

I'm an excel power user, filter now and then but DSUM is much more useful for repetitive analysis or repetitive data sets where you know the structures, common filters etc. This system analysis is more common than people realise!

@JJ_TheGreat

3 жыл бұрын

Yeah, but what is you actually want TO SEE THE DATA - and not just see the output, which is the sum, count or average. If you are filtering only to see those basic stats, then that is such an inefficient use of filtering. Filtering has other purposes.

@TigerSpreadsheetSolutions

3 жыл бұрын

A power user! We're honoured. Thanks for watching.

@Ib_h7

3 жыл бұрын

​@@JJ_TheGreat Agreed - but generally, when you're dealing with data you know your way around or testing, the outputs from a high level are more important before you *consider* whether or not you might do any further analysis and need to see the data. The comfort of seeing the data or the feeling to need to see it doesn't mean it needs to be seen :)

great!

@TigerSpreadsheetSolutions

3 жыл бұрын

Welcome!

Harika ❤️

As an alternative solution of this specific requirement, I would rather : - change my range of cells into Table because... it's a good habit when dealing with this kind of datasource (for instance, if you add lines or columns, every related formulas or pivot tables keep up, embedded formulas are automatically applied on each line, etc. Never trust a range collection of cells...) - generate Pivot Table from the table - summarize '1' value by criteria 'Home' - Add a pivot table computed column : '1' - 1.54 and call it 'Returns Home Win' And I would obtain every results for every 'Home' displayed at once, witch would be easier for comparing, sorting, further filtering, etc. You even can add some slicers and timeline features in order to ease the interaction with the Pivot Table, visualize the data through charts However, good illustration of the possibility and use of DSUM formula, I did not know it...

@TigerSpreadsheetSolutions

3 жыл бұрын

Thanks for explaining your view. Personally I find tables bring some cons too that I prefer to avoid.

@MrAZulfadzli

3 жыл бұрын

@@TigerSpreadsheetSolutions I have the opinion too, table would helps as when my data expanded, you can easily paste the additional data and all absolute reference will follow as well. Without table everytime you add on more data, you have to adjust your dsum database.. But can you explain the cons of using a table?

@channul4887

2 жыл бұрын

@@MrAZulfadzli "But can you explain the cons of using a table?" it's fucking annoying.

@MrAZulfadzli

2 жыл бұрын

@@channul4887 Err.. We must have been talking about a different thing here surely.. I'm not sure what constitute as annoying to you, but I just pressed Ctrl+T and table is created. I want to refer to the table for any formula, vlookup for example? I don't have to select row and column, just type the table name and row and column header. I want to add to the rows with exact same formatting? Just paste below it and the table automatically extended further down. I have no idea which part of excel table is annoying..

Thank you, great video! What if there are multiple criteria under HOME?

@TigerSpreadsheetSolutions

3 жыл бұрын

Thanks Sandra - I would say check out Microsoft's resources on this formula, I understand this is possible but I have never got it working consistently myself. Good luck!

@robertharris9769

3 жыл бұрын

You can use a nested SUMIFS formula: =SUM(SUMIFS($T$11:$T$711,$C$11:$C$771,{"Beagle","Poodle"},$J$11:$J$771,">1.54")). If you select a cell within your data range CTRL+A will select the current region, CTRL+T turns it into a table (in my example I named it MyTbl) =SUM(SUMIFS(MyTbl[Return Home Wins],MyTbl[Home],{"beagle","poodle"},MyTbl[1],">1.54")). 2 issues with DSUM: 1) DSUM doesn't recognize tables correctly 2) According to Microsoft from Excel 2007 you should avoid using DFUNCTIONS and use their counterparts SUM/COUNT/AVERAGE(IFS) Link docs.microsoft.com/en-us/office/vba/excel/concepts/excel-performance/excel-tips-for-optimizing-performance-obstructions

2:10 Cheater doesnt know that to select a range you can use Ctrl+* (current region without empty rows and columns) and for filter enough to select headers only and to switch on filter you can simply press Ctrl+Shift+L. :)

@TigerSpreadsheetSolutions

3 жыл бұрын

Nice shortcuts!

@Rice0987

3 жыл бұрын

@@TigerSpreadsheetSolutions You dont need even select whole range if you want to have filter on each column. Just put the mouse inside a table and press shortcut. You need to select some headers if you have wide table and you dont need to have filter on each column.

@enderaslan7998

3 жыл бұрын

also entire range selection works with CTRL+SHIFT+SPACE which are already under your fingers

Well, this is useful for summing values, but filters have way more uses than that, mainly, filter what you see....

@TigerSpreadsheetSolutions

3 жыл бұрын

True

@jharkey3

3 жыл бұрын

Had to scroll too far down to find this. Seems like such a niche use of filters.

Never heard of this! Well done . Subscribed

@TigerSpreadsheetSolutions

3 жыл бұрын

Welcome the community!

When navigating down the autofilter options you can just press E to go straight to the text box to type in your filter.

@TigerSpreadsheetSolutions

2 жыл бұрын

I like this one!

Have you tried advanced filter? That usually works faster and neater than DSUM or filters.

@TigerSpreadsheetSolutions

3 жыл бұрын

Will check it out ...

Omg... i just know it. This was a year ago... so much helpfull this formula.

@TigerSpreadsheetSolutions

Жыл бұрын

Great to hear - I find it to be the most useful Excel formula for data analysis ...

Ctr+Shift+L is the easiest way to enable filter compare to Alt+a+t

@jean-marcherard9216

3 жыл бұрын

Plus if you do it again, on the selected line, then you remove filters

@gcangeliify

3 жыл бұрын

I think the easiest way to enable filters is to add it to the Quick Access Toolbar." One click

@panksimus

3 жыл бұрын

yeah, since we are selecting range using Ctrl+Shift+Arrow key, all we have to do is press L

Is this only works for numbers or can be used for other purposes too. Let's say there are a list of companies with all their employees. Can we use this to show all employees who are working on a desired company? Also does al data must be in the same worksheet or can be in different worksheets?

@TigerSpreadsheetSolutions

3 жыл бұрын

Hi Mike - works just as well for text. The data does need to be in a single block on a single sheet, however. Good luck!