excel sumif

How to Use the SUMIF and SUMIFS Functions in Excel

SUMIF and SUMIFS help Excel users to save time and frustration by making it easy to glean valuable information from complex datasets. You can total and analyze everything from grade values to quarterly earnings without giving yourself a massive headache. In this tutorial, we’re going to cover: The difference between SUMIF and SUMIFS functions. How to use SUMIF …

Read more

MOD Function Time Extract

Extract Time from datetime with the MOD Function in Excel

I had a reader comment on my last post about how to extract time from a date-time number using the MOD function. Simple really. The syntax is MOD(number,divisor). The MOD function returns the remainder after number is divided by divisor. A simple example is MOD(5,2), which equals one (1). It works like this: five (5) …

Read more

Horizontal Dynamic Dependent Drop Down List Example

A Dynamic Dependent Drop Down List with a Horizontal Table Reference

I received a comment asking if a dynamic dependent drop-down list in Excel could have a list where the “table headers were actually rows and not columns?” Since I’ve already detailed how this is done in the article mentioned above, I’ll keep this short. The screen shot below is what I’ll be referencing. At the …

Read more

vlookup shark

The VLOOKUP Function – Inside Out

As part of Shark Week I’ve committed to write something for VLOOKUP week. (It’s what I get for using twitter.) So without further ado. I love the VLOOKUP Function in Excel. As the name implies, it’s a vertical lookup. Meaning the function will lookup data in columns. The VLOOKUP Function Arguments The VLOOKUP function has …

Read more

How to Update a List or Range without OFFSET

I avoid the use of Volatile Functions, especially OFFSET, which is commonly used to update a list or range. They can slow down the operation of your workbook. For very large workbooks with lots of data, it can be significant and irksome. Worksheet cells that use Data Validation for a drop-down list can simplify the …

Read more

Fill Down a Formula with VBA

I commented on a post that brought to light, the fact that, using the cell fill-handle to “shoot” a formula down a column doesn’t always work when the adjacent column(s) have blank cells. So I decided to share some Excel VBA code that’s used to copy a formula down to the bottom of a column …

Read more

International Short Date Formatting for the TEXT Function

I have an Excel workbook that is used in two different regions where the date format is entirely different, the US and the UK. At the top of a report worksheet I use a TEXT function to inform the user of the date range. Here’s what a US user sees: From: 6/6/2011 to 6/10/2011 Here …

Read more