All articles with 'countif()' Tag

Are you ready for an Excel challenge?

Today, your job is very simple. Just find a pattern in a text and return corresponding value.

Your Homework:

In a range we have some resource types & their billing rates.

In another range, we have some descriptions. Each description contains a resource type somewhere inside it. We need to retrieve billing rate for each description by looking up which resource type is mentioned in it.

See above diagram.

There are many web pages devoted to Excel Charts and there are many web pages devoted to Creating Dynamic Ranges, but a lot of these suffer when the data is poorly organised or non-contiguous.

Today we look at a Technique for Creating Dynamic Charts with Non-Contiguous Data.

Today at Formula Forensics, Guest author Sajan shows us how to extract a sorted, unique list of items, displaying the most frequently occurring items first, while restricting the output based on some additional criteria.

Need to count how many unique values occur in a range?
Learn how to do this and how the formula works right here.

A while ago (well more than 3 years ago), I wrote about an array formula based technique to check if a list of values have any duplicates in them.

Today, lets learn a simpler formula to check if a list has duplicate numbers.

Assuming you have some numbers in a range B4:B10 as shown below, we can use MODE + COUNTIF formulas to check if there are any duplicate values in a list.

Lykes asked Formula Forensics “How does a formula work?” Lykes has been using the formula without understanding it.
The formula counts how many times the letters from a list of letters occur in a list of words.
Today we look at a Lykes question.

We love to compare. The instinct to compare leaves no one. Even my two year old twins compare their toys with each other (and fight).

It would make Excel hugely popular if Microsoft builds a handy data comparison tool right in to it. Alas, they have customizable ribbon, 3d effects & equation editor…

Since comparison is one of the main uses of Excel, we have written extensively about it here.

But there is always one more interesting comparison problem. Today, I want to share one such problem, based on a comment left by N-Man.

Count How Many Times a List of Values Occurs in a Range
(or How Can I Simplify My Formula)

Today in Formula Forensics we look at how to count how many times a range of values occurs within a Range of cells and in the process simplify a very nasty formula.

Today we publish what is hopefully the First of many posts by guest author Luke M. it is also the Third post in the Formula Forensics series, which has now moved to a regular and new Thursday slot ?

Luke has taken up Hui’s and 3G’s challenge of explaining how the technique he regularly uses on the Chandoo.org Forums for extracting individual records out of a group.

Today Luke tells us how to extract a list of items from a larger list according to some criteria.

Chandoo is off on Holidays and once again thrown me the keys to Chandoo.org.

So this week we’re going to:

• Pull apart an Array Formula and see what’s under the hood;
• Have a look at a technique for quickly developing Custom Number Formats &
• Look at how we can use HTML Codes in posts at Chandoo.org to spice up your questions and answers.

Today is Tarun’s Problem

“I have got multiple names in each row and would like to have what name is repeated maximum number of times and how many times?”

Today we pull apart an answer to this question and see how it works.

Comparison is one of the most common things we do with Excel. Naturally, there are so many ways to compare 2 lists of data using Excel.

Today, I want to share an interesting comparison problem with you.

Lets say you run a small shop which sells some highly specialized products. Now, since your products require quite some training before customers can buy them, you keep track of all product queries and arrange demos.

After a hectic week, you are staring at 2 lists. One with product queries, another with product demos. And you want to know whether all the queries are answered with a demo or not.

Last week, we learned how to visualize Suicides vs. Murders data using Interactive charts in Excel. William, one of our readers, took this technique and applied it to Stocks. He emailed me because he has some formula issues with the stock data. Once I solved the problem, I asked him, “Can I share this with [...]

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.