We all know the good old SUM() formula. It can sum up values in a range. But what if you want to sum up only filtered values in a range? SUM() doesn’t care if a value is filtered or not. It just sums up the numbers. But there are other formulas that can pay attention to the filters. Let’s learn about them.
Summarize filtered values using SUBTOTAL & AGGREGATE Formulas – video
You may watch this video on our YouTube channel too.
Download Example Workbook
Please click here to download the example workbook. Examine the formulas and play with filters to learn more.
More about Filters, SUBTOTAL & AGGREGATE
Learn more about these features & how they can make you awesome:
Basics:
- Introduction to SUBTOTAL Formula
- Introduction to Excel Slicers – Visual Filters
- Make dynamic charts with filters
Advanced Stuff:
- How to count & sum filtered tables,
- Count & sum with criteria on a filtered list
- Sum up top 3 filtered values using AGGREGATE
- Check if a table is filtered or not using formulas
- Case study – Christmas shopping list
What do you use SUBTOTAL for?
Do you use SUBTOTAL and AGGREGATE functions? What do you use them for? Please share your thoughts in the comments section.
11 Responses to “Use Alt+Enter to get multiple lines in a cell [spreadcheats]”
@Chandoo:
One more useful trick.......
In a column you have no. of data in rows and need to copy in the next row from the previous row, no need to go for the previous rows but entering Alt + down arrow, you will get the list of data, (in asending order), entered in the previous rows...
This is another great tip. I use this all the time to make sense of some *very* long formulas. As soon as the formula is debugged I remove the break.
Great tip Chandoo!
I use this feature often and it has even gotten the, "how did you do that" response.
Thanks!
@Ketan: Alt+down arrow is an awesome tip. I never knew it and now I am using it everyday.
@Jorge, Tony: Agree... 🙂
[...] Day 1: Insert Line Breaks in a Cell [...]
how can we merge a two sheet.
excellent idea. Chandoo you are genious
Hi chandoo,
I have used ctrl+enter to break the cell. But I did not get the result.
Please tell me how can i break the cell in multiple lines.
Hi, Ranveer,
Its not Ctrl+enter to break the cell, use Alt+Enter to make it happen.
hi Chandoo....
how we can use Alt+Enter in multiple rows at the same time please reply hurry i have lot of work and have no time and i m stuck in this. 🙁
Alt+J worked once 🙁
So I found another more reliable way:
=SUBSTITUTE(A2,CHAR(13),"")
Where A2 is the cell that contains the line breaks which the code for it is CHAR(13). It will replace it with whatever inside the ""