Excel SUMIFS Problem With Blanks
April 11, 2017
SUMIFS problem with blank cells, and more, in this week's Excel news. Visit my Excel website for many more tips, tutorials and videos -- let me know if you're looking for something and can't find it there.
Someone asked for help with a SUMIFS formula last week -- some of the criteria cells were blank, and that gave strange results. Here is the original formula: =SUMIFS(D$2:D2,B$2:B2,B2,C$2:C2,C2)
It correctly sums all the values in column D, starting in row 2, down to the current row, for rows that meet the criteria. But if C2 was blank, the result was zero -- it ignored the amount in that row.
After a bit of experimenting, I found a solution that adds all the matching rows, even if C is blank. There is an IF formula as the second criteria, and it uses an empty string ("") if C is blank:
That was my first time using IF inside SUMIFS, so I'll have to find more places to try it! See more SUMIFS examples on the Sum Cells page of my Contextures website.
Last Friday, Microsoft announced the winner of their Excel World Champ data visualization contest, and I was happy to see that a Canadian won -- congratulations to Ghazanfar Abidi, from Winnipeg!
You can see all the final entries, to get inspiration for your own Excel dashboards. The Microsoft page also has links to other parts of the competition, where you can see questions and solutions from earlier rounds in the competition.
When I checked out Ghazanfar Abidi's blog, the demo in his latest blog post surprised me -- if you use the F4 or Ctrl+Y shortcut to apply fill colour to a pivot table cell, that colour will disappear when your refresh the pivot table.
I did a few experiments to see if any other cell formatting behaves the same way, but it looks like fill colour is the only problem. So, be careful if you like to use the F4 shortcut!
Here are a couple of recent Excel articles that you might find useful.
Power BI - If you want to learn more about Power BI, Ken Puls is writing a series of free Do It Yourself (DIY) BI ebooks. The first one has Excel tips for Power BI, and other books are coming soon. (Level - All)
Excel Charts - The Science Goddess explains how to build a backwards bar chart, with an example of where she would use one, and why. (Level - Intermediate)
Last week I learned about The 100 Days Project, and decided to find one family photo each day, and jot down my memories about it. These days, we take hundreds of pictures with our phones, and rarely look at them again. Here's a photo that brought back good memories -- a long ago trip to the Rocky Mountains. And yes, I sewed that bright plaid jacket myself -- it was one of a kind!
That's it for this week! If there are topics that you'd like to see covered in future emails,
please let me know.
ddalgleish @ contextures.com
Note: I am an affiliate for some of the products mentioned in this email, and earn a commission on the sales.
Last updated: July 21, 2017 9:39 AM