Daily posts of Excel tips…and other stuff
Archive for the ‘Counting and Summing Functions’ Category.
I have a range that should only contain numbers. Blanks are OK, but I need to determine if anyone “deleted” a cell by typing a space or entered text in any other way. This worksheet formula returns TRUE if there is text in the range:
=COUNTA(A1:C6)-COUNT(A1:C6)>0
COUNTA counts all the non-blank cells while COUNT counts [...]
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 [...]
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 [...]
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 [...]
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 [...]
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 [...]
Prevent #REF! errors from creeping into your running total columns.
I started fiddling around with a Sudoku spreadsheet this week. I got a Sudoku book in my Christmas stocking. I didn’t have terrible difficulty solving most of the puzzles, but it’s obvious after the first one that Excel could be a nice tool for keeping track of which numbers can and cannot go [...]
