All articles with 'countif()' Tag

Over at PTS Blog, my dear friend & charting guru, Jon Peltier has an interesting post on using dot plots to visualize Murders & Suicides data by US States.

Not that murders & suicides fascinate me, but I wanted to play with this data myself and see how we can visualize it. So I emailed Jon and asked him to share the raw data. Being a lovely chap Jon is, he immediately sent me the data. So here we are, playing with gory data on a Friday.

Suicides & Murders by US States – An Interactive Excel Chart

You can see a demo of the chart I came up with above. Read on to learn how this chart is constructed.

Last week, we had a home work on Calculating Donation Summaries using Excel Formulas. This is a good case where array formulas can help us. So today, we will learn how we can use Array Formulas to compare lists of values and calculate summaries. Towards the end of this post, you can see a video that explains the solution to Donation Summary Calculation problem.

Situation

We know that VLOOKUP formula is useful to fetch the first matching item from a list. So what would you do if you need 2nd (or 3rd etc.) matching item from a list?

For eg. If you have below data, and you want to find out how much sales John made 2nd time, then VLOOKUP formula becomes quite useless. Or is it?!?

Read more to find how to solve this.

Ok, you have learned how to write vlookup formulas. You have also seen some pretty interesting examples of it (1, 2).

But how do you write better VLOOKUP formulas?

Here is a list of 6 tips that work wonders with VLOOKUP writing.

Comparison of lists of data is something that we do all the time. Today, lets learn a few tricks that you can apply immediately to compare 2 lists using Excel. This post discusses how to compare two lists with formula based rules. If you just want to quickly highlight common values, click here. If you […]

Anyone working on the data using excel will know the importance of lookup formulas. They are vital for making almost any spreadsheet or dashboard. That is why when my friend John Franco, who maintains Excel-Spreadsheet-Authors.com, wrote to me about his new book Excel lookup toolbox I was truly excited. In this post I am going to share my review of this product.

We all know that data validation is a very useful feature in Excel. You can use data validation to create a drop-down list in a cell and limit the values user can enter. But, do you know that you can use data validation in a multitude of ways to prevent users from entering wrong data?

Here is a practical application: Prevent users from entering duplicate values in a range of cells.

For eg. you are making an invoice. Wouldn’t it be cool if Excel prompted you when you enter a duplicate line item so that increase the item quantity instead of repeating it.

Tracking issues and risks is where most of the project management time goes. Once the project planning and organizing activities are in good shape, most of the project management activities are around risk management and issue tracking. In this installment of project management using excel, we will learn how to create a simple issue tracker template using excel and how to analyze issues using excel.

It is no exaggeration that knowing excel formulas can give you a career boost. From someone starting at the long list of numbers, you can suddenly become a data god who can lookup, manipulate and analyze any spreadsheet.

So when our little excel blog hit the 5000 RSS Subscriber milestone, I celebrated the occasion by asking you to share an excel formula through twitter or comments with rest of us. And boy, what an excellent list of formula tips you have shared with us all.
Here is the complete list of entries for the twitter formula contest.

Here is an excel formula quick tip that can come handy when you need to count the number of unique values in a range of cells. Assuming we have a list of values in the range:B5:B15 and we want to know how many unique values are there,
you can use the almighty SUMPRODUCT formula like this: SUMPRODUCT(1/COUNTIF(B5:B15,B5:B15)).

Read the rest of this post to understand how the formula works. You can also find resources to work with duplicate values in excel.

Learn how to generate invoice numbers, tax codes etc. using Microsoft Excel. In this example we will take a real life example shared by Michelle and findout how we can generate invoice numbers using excel formulas. Read more to learn and download the example workbook.

In this installment of our spreadcheats series, we will learn how to use array formulas and findout if a list has any duplicate items. We will use Countif and array formulas to do this …

Data validation is a great way to keep your users informed about possible values in a cell and guide them to select something appropriate. As part of the spreadcheats series, in this post we will discuss 2 advanced data validation techniques that can help you when you are modeling a complex worksheet.

If for every countif() I write excel paid me a dollar, I would be a millionaire by now. It is such a versatile and fun formula to work with that I have decided to write about it as third post in our spreadcheats series. Using COUNTIF() to replace pivot tables: We all know that you […]

Excel has various functions, including functions to calculate inverse cosine of a given value, to multiply 2 matrices, to estimate the internal rate of return. But, most of us(well, just me then..) use just about 5-6 formulas to do our jobs. And IF formulas are a majority of these, so it doesn’t harm to learn […]