Excel - Is This a New Thing?
June 15, 2021
Thank you for reading the news, and you'll get the next email on June 29th.
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
You can use the SUMIFS function to get a total, based on a date range, or other criteria.
To make it easy to change the date range later, I like to put the start and end dates on the worksheet -- they're in cells D2 and E2 in this example.
And here's the SUMIFS formula that's in cell D5, to get total units sold in that date range:
Most of the time the sum and criteria cells are in vertical lists, like the Date (A2:A9) and UnitsSold (B2:B9) shown above.
But, did you know that you can use different shapes, as long as the ranges follow these 2 rules?
For example, you could use this layout, where each range has two rows, and 4 columns, even though the ranges don't line up with one another!
You can see more SUMIFS and COUNTIFS tips and examples, on the Excel Sum and Count in Date Range page on my Contextures site.
Is this a new sorting feature in Excel pivot tables, or just something that I never noticed before? I'm using Excel 365, and don't have any older versions to test in.
If you have an older version of Excel, could you try this for me please? If you need a file to test with, get sample #2 on my Pivot Table Sorting page.
What happened when you tried that?
In Excel 365, to my surprise, the columns sorted Z-A, instead of the numbers. I don't remember that happening before, but there's a lot I don't remember! ;-)
Here are some Excel-related links that you might find useful or interesting.
Excel Battle: Did you watch the Financial Modeling World Cup Multi-player Battle on June 8th? If not, you can watch the recording on YouTube, and get the sample files ("Cake to Bake") to try the challenge on your own. (Level - All)
Secret Features: Excel used to have a hidden flight simulator, but things have gone downhill since then! In this Tech Republic article, DATEDIF is Excel's secret feature. Meanwhile, Google Sheets can be magically rainbow coloured. (Level - All)
Also see: Previous Newsletter Issues
Despite the surprise snowfall that we had a couple of weeks ago, our garden is thriving. Here are the first flowers from the perennial garden kit that I planted in May - Bleeding Hearts. Wikipedia says this plant is native to Siberia, so it should do well here in Canada too.
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2021/20210615ctx.html
I'll also post any article updates or corrections there.
That's it for this week! If you have any comments or questions, send me an email.
Last updated: July 25, 2021 2:08 PM