Category: Excel Formulas

Excel Count With 2 Criteria-SUMPRODUCT

Have you ever had trouble trying to count items in an Excel list, based on two criteria? See how to use the Excel SUMPRODUCT function to get the count that you need

Use SUMPRODUCT to Count

Instead of using the COUNT function, or the COUNTA funtion, you can use the SUMPRODUCT function to count items based on 2 criteria.

In the example shown below, the SUMPRODUCT function is used to count the rows where :

  • the item sold is “Pen”
  • AND the quantity is greater than or equal to 10

Add SUMPRODUCT Formula

This solution will work in any version of Excel, including Excel 2003 or earlier, where there COUNTIFS function is not available.

  1. Select the cell in which you want to see the total
  2. Type an equal sign (=) to start the formula
  3. Type:   SUMPRODUCT(–(
  4. Select the cells that contain the values to check for the first criterion. In this example, cells A2:A10 will be checked
  5. Type the first criterion:   =”Pen”
    Note: Because this is a text criterion, it is enclosed in double quote marks.
  6. Type ),–(
  7. Select the cells that contain the values to check for the second criterion. In this example, cells B2:B10 will be checked
  8. Type the second criterion:   >=10
    Note: Because this is a numerical criterion, it is NOT enclosed in double quote marks.
  9. Finish with closing brackets: ))
  10. The completed formula is shown in the screen shot below.
    • =SUMPRODUCT(–(A2:A10=”Pen”),–(B2:B10>=10))
  11. Press the Enter key to complete the entry
Excel Count multiple criteria with SUMPRODUCT
Excel Count multiple criteria with SUMPRODUCT

Use Cell References

Instead of typing the criteria in a formula, you can refer to a cell, as shown in the second formula below.

  1. Use typed criteria:

=SUMPRODUCT(–(A2:A10=”Pen”),–(B2:B10>=10))

2. Or use cell references:

=SUMPRODUCT(–(A2:A10=D2),–(B2:B10>=E2))

More Excel Count Examples

There are many more examples, written steps, and videos for counting in Excel on my Contextures website.

COUNT / COUNTIF Examples

Count Criteria in Other Column

Count Specific Items

Count Specific Items in Cell

Count Cells With Specific Text

____________

Excel INDIRECT Function-Lock Absolute Reference

In an exclusive World Movie Premiere, here is the first (and probably last) instalment in Excel Theatre. It’s an animated short, named Absolute Reference Problems. Watch for it in this year’s Oscar nominations!

Video: Absolute Reference Problems

Please note the giant spreadsheet in the background of the video below. I think that grid adds to the tension in this dramatic presentation.

Just so you know – the video’s dialog is corny, the actors are wooden, the plot is weak and the costumes are pitiful. Other than that, it’s pretty good. 😉

The INDIRECT Function

If you haven’t used INDIRECT before, it’s a formula that returns a reference to a range, based on a text string.

As the video pointed out, you can use an absolute reference to a cell, to “lock” the reference, and keep if from changing if you copy the formula to a different cell.

However, if the referenced cell moves, the absolute reference changes to match the new location.

Two Worksheet Formulas

For example, in the screenshot below:

  • cell C2 contains an absolute reference to cell A1
  • cell C3 contains an INDIRECT formula that refers to cell A1.
absolute reference to cell A1
absolute reference to cell A1

Insert Row Above

If you insert a blank row at the top of the worksheet, the formula in cell C2 changes, and it now refers to cell A2.

However, because it’s a text string, the reference in the INDIRECT formula does NOT change. It returns a zero because cell A1 is now empty.

Indirect02

Using the INDIRECT Function

You can use INDIRECT in many ways. For example:

  • dependent data validation lists
  • to prevent a cell reference from being affected by a move
  • create cell references from a combination of cell values and text.

For more information on the INDIRECT function, and examples of how to use it, please visit the INDIRECT Function page on my website.

Video Transcript

In case you want to read along with the animated video characters, here is the full transcript. Have fun!

Oh No!

What is wrong?

I used an absolute reference to cell A1

Good! Your formula should always refer to that cell

I thought so too, but then I inserted a new row at the top of the worksheet

That should be okay

It isn’t! Now my formula refers to cell A2

Oh no! The total could be wrong now.

How could that happen? What is the point of using an absolute reference if it can change?

Maybe you should try an INDIRECT instead

D’oh! Why didn’t I think of that?

I will change it to an INDIRECT formula, and it will always refer to cell A1, thanks!

Video Note

How did I end up making this silly Excel video? Well, I should have been working all day, but decided to take a bit of time to relax and catch up on some reading (of RSS feeds).

On a technology blog, I saw a link to XtraNormal, where you can write, cast and direct an animated movie.

That sounded like more fun than working, so off I went.
________________

Excel VLOOKUP-Change Percent to Letter Grade

Usually when I use VLOOKUP, I want to pull information for an exact match.

For example, if I enter a customer number in one cell, I want the customer name in the adjacent cell. I don’t want the name of a customer whose number is CLOSE to the one that I entered.

Continue reading “Excel VLOOKUP-Change Percent to Letter Grade”

Split First and Last Names in Excel

If you have a list of names in Excel, with first and last names separated by a comma, you can use an Excel feature to split first and last names into separate columns.

See more ways to split names, and get an Excel sample workbook, on the Names, Split/Reverse First and Last page on my Contextures site.

Continue reading “Split First and Last Names in Excel”

Create Bingo Cards in Excel

It’s Friday, and things are slow at the office. To liven things up, you could create bingo cards in Excel, and organize a game during the lunch hour.
In this example, there are three cards, each with a set of random numbers. You’ll need one of those numbered ball popper machines though, or create a number selector in Excel.
Continue reading “Create Bingo Cards in Excel”

Sports Analysis in Excel

One of my clients coached his women’s hockey team to a provincial championship last weekend (and this is the medal they were awarded). Congratulations to him and the team!

I don’t think he used Excel in planning his coaching strategy, but I’m sure Excel has other uses in sports.

GoldMedal

Assign Baseball Players

For example, one of the Excel sample files on my website lets you assign baseball players for each inning. After you assign a player in an inning, that player’s name is removed from the drop down list for the inning.

You can’t accidentally assign the same player twice. You can download the zipped file here: DataValPlayerInnings.zip 3kb

assign baseball players for each inning
assign baseball players for each inning

Schedule Tee Off Times

There are also a couple of sample Excel file in which you can assign tee off times for a golf event. One is worksheet based, and uses an Advanced Filter to schedule selected players in specific time slot. Download it here: GolfTeeOff.zip 11 kb

The other version has a UserForm in which to enter the tee off data: GolfTeeOffForm.zip 16 kb

Analyze Walking Targets

In another sample file, you can record the number of steps you’ve walked each day, and formulas will calculate if you’ve reached the thresholds that you’ve set.

It keeps track of your streaks, such as the greatest number of consecutive days that you’ve achieved each target.

You could adapt this to other sports or personal goals.

Download the file here: WalkTrack.zip 8 kb

Analyze Walking Targets
Analyze Walking Targets

Hockey Analysis

Sadly, I don’t have any hockey sample files, but I found a Hockey Analytics site that has Excel and pdf files you can download.

For example, there are several Excel files with calculations for Individual Player Contributions, and a few with Personal Goals Against Averages.

If you like statistics and hockey, you’re sure to find something of interest. But remember, as the Hockey Analytics site points out:

Baseball is a game of a limited number of states…It can be modeled accurately in discrete steps…This ain’t the case with hockey. Hockey is fluid and can only be modeled approximately…Hockey statistics are terribly incomplete.

_____________________

Convert Excel Numbers to Roman Numerals

Recently, I read a business article that said you should “become a Roman” to succeed in business. By that, the author meant, “be disciplined and willing to keep fighting”.

Excel  ROMAN Function

Maybe that’s why we have the Excel ROMAN function! It will quickly convert a worksheet number into Roman numerals.

That frees up our time, so we can “keep fighting” to fix other problems in our Excel files.

In the sections below, I’ll show you how the ROMAN function works. And I found a couple of fun facts about ROMAN, that might impress your friends and co-workers. (Or not!)

Excel ROMAN Function Syntax

Here are the two arguments in the ROMAN function:

  1. number: an Arabic number, between 0 and 3999, that you want to convert to Roman numerals.
  2. form: (optional) the type of conciseness that you want to display.

Tip: Read about the standard Roman numeral format, and other forms, on the Wikipedia Roman Numerals page.

1. Number Argument

For the number argument, you can type the number into the formula, or refer to a cell that contains a number between zero and 3999

At first, I incorrectly assumed that the 3999 was an Excel limit, but that’s not the reason. Instead, I learned that 3999 is a Roman numeral limit.

  • The largest number that can be represented in standard Roman numeral form is 3999
  • That number is written as MMMCMXCIX in Roman numerals
number 3999 as Roman numeral
number 3999 as Roman numeral

2. Form Argument

On those rare occasions when I use the ROMAN function, I always omit the second argument, form.

  • If you omit the 2nd argument, or use TRUE or zero, the result is a classic Roman numeral, that you probably learned in school.
Form Argument Omitted

In the screen shot below, I entered 7 numbers in column A, and the ROMAN function, with the 2nd argument omitted, in column B.

NOTE: Those are the only 7 characters used to create any Roman numeral:

  • I, V, X, L, C, D, M
Characters used for Roman numerals
Characters used for Roman numerals

Levels of Conciseness

For the Form argument, you can also use numbers between 1 and 4, as well as FALSE.

  • Numbers 1 to 4 create more concise versions of the Roman numeral.
  • The higher the number, the greater the level of conciseness.
  • FALSE is Simplified form, the same as number 4

Some numbers will show different Roman numeral, depending on the form argument. Other numbers will have the same Roman numeral for all forms.

  • For example, in the screen shot below, number 1499 has a different Roman numeral for each form.
  • The number 115 (not shown) has the same result for all forms

Also, the results below show that

  • TRUE is the same as zero
  • FALSE is the same as 4
ROMAN function with form argument from 0 to 4
ROMAN function with form argument from 0 to 4

Excel Function Tutorials

These tutorials, on my Contextures site, show how to use some of the most popular Excel functions.

To see full list of Excel functions, visit the Excel Functions List page.

1 — How to Sum Cells – Start with the SUM function, then try SUMIFS and more!

2 — Count All or Specific Cells – Do a simple count, or count based on criteria

3 — How to Do a VLOOKUP – Find a lookup item in a table, such price for specific product

4 — Lookup With Criteria – Use formulas to get values from a lookup table, based on multiple criteria

5 — Combine Text & Numbers – Use formulas to combine values text and numbers from different cells

__________________________

Working With Dates In Excel

This week I’ve been working on date formulas, from very simple ones, to complex formulas that calculate workdays per month, based on start and end dates that can span several months.

Extract Information from a Date

Many times I need to pull a bit of information from a date, such as the year, month or weekday.

In the section below, I’ve listed the sample Excel formulas I would use, to calculate specific dates in Excel.

  • For all formulas, the date — December 29, 2008 — is in cell A2.
Date Calculation Formulas
Date Calculation Formulas

Date Calculation Formulas

Here are the formulas to extract information from a date in cell A2.

To Calculate

The Formula

The Result
Year =YEAR(A2) 2008
Month Number =MONTH(A2) 12
Month Name (short) =TEXT(A2,”mmm”) Dec
Month Name (long) =TEXT(A2,”mmmm”) December
Day of the month =DAY(A2) 29
Weekday Number =WEEKDAY(A2,1) 2
Weekday Name (short) =TEXT(A2,”ddd”) Mon
Weekday Name (long) =TEXT(A2,”dddd”) Monday
Year Month =TEXT(A2,”yyyy mm”) 2008 12

Using Calculated Dates in Pivot Table

If I plan to create a pivot table from data that contains a date field, I usually calculate the year and month in the source data.

Then I can add those fields to the pivot table, instead of the individual dates.

Yes, the pivot table could automatically group the individual dates by year and month, but that can limit other functions in the pivot table.

For example:

  • if two pivot tables are based on the same data, grouping one pivot table by month would cause the other pivot table to also be grouped by month.
  • if a field is grouped, you cannot add calculated items to the pivot table
pivot table error message - cannot add a calculated item
pivot table error message – cannot add a calculated item

Video: Pivot Table Grouping Tips

This video shows how to group pivot table dates by month and years, and how to group text items manually.

There are written steps, and an Excel workbook, on the How to Group Pivot Table Data page, on my Contextures site.

Pivot Table Grouping

For more information on Excel pivot table grouping, go to the How to Group Pivot Table Data page, on my Contextures site.

There are examples for grouping dates, number and text fields. You’ll also see solutions for fixing pivot table grouping problems, such as the error message, “Cannot group that selection”