Counting Number Of Unique Entries (CountIf Function)

Excel function tutorials

I received a question that made me think of something I had tried to do in a spreadsheet a few months ago but never got around to discuss on this blog. Suppose that you have a large set of data that looks like:

There are a large number of things that could be done with such a set of data but one always tricky aspect is getting answers to questions that involve multiple columns. For example, if someone were to ask you, how many different locations of the store sold at least 1 Early Grey Russian black tea box on each day? I cannot simply add up the number of sales since some stores sell multiple boxes. What could I do? I’ll add 2 columns and include the “countif” function which will give me my answer:)

First, I will create a column with a “UniqueID” with the concactenate:

Then, I will use the “countif” function to see if the ID is unique:

As you can see, all numbers that are “1″ are non-Unique. I can then manipulate that column in different ways

As always, you can download the spreadsheet here:)

Nest if Functions – It’s All About Doing It Step By Step

Excel function tutorials

I often get these types of questions where someone is trying to do a nested if function but having trouble doing so. Here is one such example:

“I am trying to write a and/if statement to read if b2=”manager” and d2=”b” and h2

Is there some help someone could provide me with this?"

The only tricky part in doing this is splitting the problem into different parts and resolving those one at a time.

Basically here, I'm looking to see if:

-all 3 conditions are met - if so, display:

The first condition is:

=IF(B1="manager",1,0)

The second condition is:

=IF(D1="b",1,0)

After nesting both together, my new formula would be:

=IF(AND(B1="manager",D1="b"),1,0)

Then, I'd add the final condition:

=IF(AND(B1="manager",D1="b",H1<=740),1,0)

Simple enough? I'll then replace "1" by under:

=IF(AND(B1="manager",D1="b",H1<=740),"under",0)

Here is a file with such an example:

How I send Outlook Email Through Excel

Excel 2013, Excel macros

One of the main things that I like to do when automating excel files is getting the whole process done. What do I mean? Not only creating a file and saving it to a drive, but also sending it out to the group that I need it to get to on a daily basis.

For example, I have this file that creates a list of tennis players:

Suppose that I run this file every day, save it to a drive and send it to 4-5 people? I can do all of that (more or less) from my excel file. How? By helping Microsoft Excel communicate with Outlook.

It’s similar to what you’d do when building any other macro. I personally use 2 different steps:

#1-Add the “standard” email funfction to my file. For this to be done, I always copy the same text to my files:

Then, I can simply ad the relevant code to my existing macros. Here is a simplified version that would send out a file:

It’s fairly straightforward and it works:) Once I click the button, an email shows up and I can simply press “send”

You can download the spreadsheet here!

Converting PDF’s to Excel

Excel 2013

Ahhh! I’ve personally been looking for this for a very long time. I have been working on a tennis website for years and always had a very big issue trying to convert pdf files where the tournament draws would be into excel files where my macro’s could help produce content. Basically, I would get a file like this:

Into an excel file. I personally tried at least 15 different solutions to do this, some that required payment and never had any luck in getting a good solution. Finally, I was directed to a program that worked out well. It’s not free but not very expensive either. You can try it here:

http://www.investintech.com/able2extract.html

I get the following result:

Not bad right? This was done using the free trial version.

Able2Extract

Doing An IRR function in Excel manually Using The Goal Seek Function

Excel function tutorials

The other day I got an interesting question from a reader. He was trying to do an IRR (internal rate of return) but had one specification. An IRR function assumes that all cash flows are at the same period of the year. He wanted something more precise that would account for the fact that some cash flows might be at the start, beginning or middle of the year. I suggested that he try to do an IRR manually and decided to give it a try myself.

First, take a look at a traditional IRR function result:

Then, I decided to get the same result but with a function instead. How?

An IRR function is the rate of return for break-even. So I created a formula manually that would calculate the “NAV”. When that NAV would become $0, I would have the correct IRR. Look at this screen:

Then, I used the goal seek function to set the nav to $0.

As you can see, I get the same result:

Then, I did the same exercise but changed the years to reflect the period. So Q1 of the 3rd year would become 2.25, etc. See the result here: