Contextures News 20151020

Count with multiple criteria, calculated item problems

October 20, 2015

Combine data, add pictures, better reports, 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.

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Pivot Table Calculated Items

In this week's email question, Stephen had a problem with a pivot table calculated item. It correctly showed the difference between Budget and Actual amounts, but:

• "it performs the calculation on every single row, even when there is no underlying data...and displays the row with a zero. Is there a way to suppress these rows?"

Unfortunately, that's one of the side effects of using a Calculated Item, and there's no way to change that behaviour. The best you can do is filter out the rows with zero total, but that will also hide any valid rows that have a zero value. Read more about it on my pivot table blog.

Count Items Based on Criteria

With the COUNTIF function, you could count all the "East" region orders in a column. If you want to count all the order for "East" region, where a note has been entered in the Problems column, you can use the COUNTIFS or SUMPRODUCT functions. Read more about counting with multiple criteria.

Spreadsheet Hall of Fame Update

Saturday, Oct. 17th was Spreadsheet Day, and I hope you had fun celebrating. To vote for the spreadsheet programs that you'd like to add to the Hall of Fame, click here.

Excel Dashboards

Mynda Treacy is offering two free one-hour webinars this week -- 1) How to Build Excel Dashboards, and 2) Dashboards with Power Query and Power Pivot. Get the details, and sign up for a date and time that is convenient for you.

Excel Articles

Here are a couple of Excel articles I read recently, that you might find interesting and useful.

Group By Times -- If you have a table with time-stamped items, Jon Acampora shows 3 ways that you can groups them into time blocks. He uses a pivot table, the FLOOR function and VLOOKUP. (Level - Basic)

Logical Functions -- In the PC World blog, JD Sartain takes an in-depth look at Excel's logical functions -- AND, OR, NOT, and a new function called XOR. She starts with simple definitions, then shows examples of how to use them. (Level - Intermediate)

More Excel Articles -- Find links to more articles in the latest Excel Weekly Roundup on my Contextures blog. And for a bit of spreadsheet humour, you can see what people are saying about Excel, in my weekly collection of tweets.

Fall Colours

We're enjoying the fall colours here in Canada, and they probably won't last much longer. Snow is already falling not too far north or here. I snapped a few pictures when we were in the park last week, and my grandson thought the tree would look better with a Blue Jay in it. He obviously inherited my taste for bad jokes.

NOTE: If you have any problems with the links in the email, you can see this newsletter on my website -- copy this link and paste it into your browser: https://www.contextures.com/newsletter/excelnews2015/20151020ctx.html

That's it for this week! If you have any comments or questions, send me an email.

Debra Dalgleish
dsd@ contextures.com

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.