Your Excel Tips - Count With Criteria
Oct 20, 2015
Count with multiple criteria, calculated item problems, and much more, in this week's Excel news.
- Debra - ddalgleish@contextures.com
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.
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
_____________
See more recommended Excel products.
________________________
Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.
__
Contextures Inc., Copyright ©2018
All rights reserved.
