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.
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:
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.
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.
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.
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.
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.
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.
Last updated: December 13, 2019 4:26 PM