Daily Dose of Excel

Recently I got a workbook with this formula:

=F57+F52+F43+F35+F25+F16+F10+F8+F59+F61+F60

I’m sure you’ve seen a few formulas like this. I know I have. I may have even made one or two. There is a better way, however. Usually a formula like this means there are several lists in the column with subtotals and this formula returns a grand total. The problem is when you want to add a new list or item and include it in the total. It’s not hard to add a +F62 to the above formula, but if you forget to do it, it’s not always obvious that the formula is wrong.

I changed the above using the SUBTOTAL function (with 9 as the first argument because I’m summing). One really nice feature of SUBTOTAL is that it ignores any cells that have a SUBTOTAL function in them. I can SUBTOTAL the whole range, and as long as I’ve used SUBTOTALs within the range, I don’t have to worry about double counting. If I add a new item in the middle, the SUBTOTAL range will adjust to accommodate.

Here’s an example of the old way:

Here’s the same example using SUBTOTAL:

Amy asks

I want to count every entry in a column (including duplicate entries) for all entries except the repeat (“) sign

Note that there are three double-quotes after the not-equal sign. To include a double-quote inside a double-quoted string, you use two double-quotes right next to each other. It’s similar in VBA. Here’s another way:

=COUNTIF(A1:A7,”<>” & CHAR(34))

I need help. I have this:

I can tell my how many unique fruits are in column A with this array formula

E4: =SUM(1/COUNTIF(A2:A13,A2:A13))

And I can tell how many rows have both Apple in column A and 1 in column B with this array formula

E5: =SUM((A2:A13=”Apple”)*(B2:B13=1))

But I can’t seem to figure out the formula to tell me the count of unique fruits that have a 1 in column B. The answer is two, apple and peach. What’s the formula that gets me there?

A few days ago I solved a problem that I've been working on for more than five years: How to create a multi-cell array formula that returns a sorted list of text entries. It's easy enough to do this with numeric entries, but the text solution has eluded me.

Range A2:A21 is named data. Select C2:C21 and type (or paste) the formula below. Then press Ctrl+Shift+Enter (rather than Enter).

=INDEX(data,MATCH(ROW(INDIRECT("1:"&ROWS(data)))-1,COUNTIF(data,"<" &data),0))

Text entries made in the data range appear in the C2:C21 range in alphabetical order.

Caveat: It doesn't work for numeric entries, or duplicated text entries.

If you'd like to get rid of the #N/A display, wrap it up in an IF function:

=IF(ISNA(INDEX(data,MATCH(ROW(INDIRECT("1:"&ROWS(data)))-1,COUNTIF(data,"<" &data),0))),"",INDEX(data,MATCH(ROW(INDIRECT("1:"&ROWS(data)))-1,COUNTIF(data,"<" &data),0)))

Here's how to do it using the new IFERROR function (and this may be the best reason yet to upgrade to Excel 2007):

=IFERROR(INDEX(data,MATCH(ROW(INDIRECT("1:"&ROWS(data)))-1,COUNTIF(data,"<" &data),0)),"")

Why did it take me so long to figure this out? I was fixated on an entirely different approach — one that I'm convinced would never work. I hadn't thought about it for about a year, but then all of a sudden it came to me.

I can't think of any good reason to actually use this, but I feel pretty good about figuring it out.

Most users know that Excel stores no more than 15-digits of a value. Therefore, if you enter items such as credit card numbers, you must format the cell as Text, or precede the number with an apostrophe.

Try this. Enter the following 18-digit strings into A1:A3. They differ only in the last three digits. Precede each with an apostrophe, or Excel will replace the last three digits with zeros.

123456789012345111
123456789012345222
123456789012345333

Enter this formula in B1:

=COUNTIF(A1:A3,A1)

The formula counts the number of entries that are the same as cell A1. It should return 1, but it actually returns 3. COUNTIF is ignoring the last three characters of the strings. You get the same result if you format the cells as Text, and even this formula returns the wrong answer:

=COUNTIF(A1:A3,"123456789012345111")

However, this array formula works (enter it with Ctrl+Shift+Enter):

=SUM((A1:A3=A1)*1)

I use Excel to maintain my projected personal and corporate cash flows based on my current balance and projected income and expenses throughout the month.

Column B contains income, column C contains expenses, and column E contains my running total. When I first created my worksheet, I used formulas such as =E4 + B5 - C5 to calculate the running balance (in this case for cell E5) after figuring in some income or an expense. The problem came when I deleted a row. For example, assume that I deleted the 8/2/2006 transaction from the list in the previous graphic.

When you delete a cell referred to in a formula, Excel displays the #REF! (bad reference) error code. Editing the formula in the first error cell causes Excel to fix the problem in every affected cell, but there is a way to avoid the errors entirely. To do so, create an OFFSET function to look up the previous balance, income (if any), and expense (if any). Here’s the formula I use for cell E5:

=OFFSET(E5,-1,0)+OFFSET(E5,0,-3)-OFFSET(E5,0,-2)

Managing running totals and balances using Excel 2003 data lists, or Excel 97-2002 worksheets, is somewhat involved. You can find a free article documenting the process on my web site.

All the best.

Curt