Hi - Dave here.
Happy Friday!
A couple of weeks ago, a reader asked me how to conditionally sum values in more than one range. More specifically, how to sum quantities by color, as seen in the workbook below.
Traditionally, this is a tricky problem in Excel because functions like SUMIFS aren't made to accept more than one range. Instead, you need to use SUMIFS more than once. For example, to sum the quantities for Red, you could use a formula like this:
=SUMIFS(C5:C12,B5:B12,"red")+SUMIFS(F5:F11,E5:E11,"red")
This isn't much fun, especially as you add more ranges. Isn't there a better way?
Yes, the latest version of Excel provides great new tools for this problem. In fact, we can calculate everything in one step like this:
=LET(data,VSTACK(B5:C12,E5:F11),GROUPBY(CHOOSECOLS(data,1),CHOOSECOLS(data,2),SUM))
[
Download the workbook and read the full explanation]
Although this formula is longer, it avoids a lot of manual effort. VSTACK combines the ranges, and GROUPBY builds the table. The result is like a lightweight Pivot Table that does not need to be refreshed. Read the article above for a full explanation. Then, download the workbook to try it out yourself.
Note: VSTACK and GROUPBY are only available in Excel 365. However, I also explain how you can solve this problem with the SUMIFS function and a more manual approach, and both methods are included in the workbook.
Excel formulas
We maintain a list of over 1000 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
The Exceljet newsletter is free and sent weekly on Fridays. Each week, I take a detailed look at how to solve a specific problem with an Excel formula. You can sign up on our home page.