Contextures

Contextures News 2021-06-15

Excel - Is This a New Thing?

June 15, 2021

Date range totals, old or new feature, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

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.

Date Range Totals

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:

  • =SUMIFS($B$2:$B$9, $A$2:$A$9, ">=" & $D$2,$A$2:$A$9, "<=" & $E$2)

SUMIFS formula for 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?

  • All sum and criteria ranges must be the same size (same number of rows and columns)
  • Each sum or criteria range must be a contiguous block of cells

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!

horizontal sum and criteria ranges

You can see more SUMIFS and COUNTIFS tips and examples, on the Excel Sum and Count in Date Range page on my Contextures site.

New or Old?

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.

  • Go to a pivot table with 2 or more fields in the Values area.
  • Click in one of the Value field headings
  • In the Ribbon at the top of Excel, click the Data tab
  • In the Sort & Filter section, click the Z-A button, to sort the values largest to smallest
    • Or, click the Z-A button in the Quick Access Toolbar, if you have it there

pivot table sort values

What happened when you tried that?

  • Did the numbers sort, or did the Value headings sort?
  • Which version of Excel are you using?

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! ;-)

pivot table value headings sorted

Excel Articles

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

Garden Hearts

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.

weekly photo

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.

Debra Dalgleish
dsd @contextures.com
ctxdebra @gmail.com

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.

contextures newsletter info

 

Last updated: July 25, 2021 2:08 PM