# All articles with 'countif()' Tag

## Suicides & Murders by US States – An Interactive Excel Chart

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.

Continue »

## Comparing Lists of Values in Excel using Array Formulas

Published on Jun 14, 2011 in Excel Howtos, Learn Excel

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.

Continue »

## Getting the 2nd matching value from a list using VLOOKUP formula

Published on Nov 10, 2010 in Learn Excel

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.

Continue »

## 6 Tips for Writing Better VLOOKUPs

Published on Nov 2, 2010 in Learn Excel

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.

Continue »

## Become a Comparison Ninja – Compare 2 Lists in Excel and Highlight Matches

Published on Jun 17, 2010 in Excel Howtos, Featured, Learn Excel

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 […]

Continue »

## Product Recommendation – Excel Lookup Toolbox

Published on Nov 5, 2009 in Learn Excel, products

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.

Continue »

## Prevent Duplicate Data Entry using Cell Validations

Published on Oct 26, 2009 in Excel Howtos

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.

Continue »

## Issue Trackers & Risk Management using Excel [Project Management using Excel – Part 5 of 6]

Published on Sep 8, 2009 in Charts and Graphs, Featured, Learn Excel

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.

Continue »

## 29 Excel Formula Tips for all Occasions [and proof that PHD readers truly rock]

Published on Aug 24, 2009 in Excel Howtos, Featured, Learn 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.

Continue »

## Count the number of unique values in a range [Quick Tip]

Published on Aug 6, 2009 in Excel Howtos, Learn Excel

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.

Continue »

## Generating invoice numbers using excel [reader questions]

Published on Jul 20, 2009 in Excel Howtos, Learn 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.

Continue »

## Using Array Formulas in Excel – Find if a list has duplicate items

Published on Mar 25, 2009 in Excel Howtos, Learn Excel

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 …

Continue »

Published on Nov 25, 2008 in Learn Excel

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.

Continue »

## Eight ways to exploit COUNTIF() … and sumif() too [spreadcheats]

Published on Nov 12, 2008 in Learn Excel

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 […]

Continue »

## Learn Excel IF Formula – 5 Tricks you Didnt know

Published on Jun 9, 2008 in Featured, Learn Excel

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 […]

Continue »

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.