Qustions about "COUNTIF" and "SUMPRODUCT"

**grg** · 11-21-2008, 04:14 PM

I have questions about the two Excel commands listed in the title of this thread as well as a general question about Excel 2008 for the Mac.

Suppose I have a list of 6000 items (numbers or text) in column C. I can determine how many times each item appears in the list by inserting the following command into the first 6000 cells of another column (say, column D).

=COUNTIF($C$1:$C$6000,$C$1:$C$6000&"")

Specifically, let's say that the above command is in D1 . . . and it is identical with the command in D2, which is identical with the one in D3, and so on. Yet, the number in D1 clearly refers to the value of C1, the number in D2 clearly refers to the value of C2, etc.

My questions about the above command are as follows:

How does the command work?
What does the &"" do?

I can also determine how many unique values are in the column-C list by using the following command:

=SUMPRODUCT((C1:C6000<>"")/COUNTIF(C1:C6000,C1:C6000&""))

Here are my questions about this command:

How does it work?
What does the forward slash (/) do?
What does the &"" do?

I also have another unrelated question: Does anyone know why Excel 2008 is so slow?

Thank you very much!

Greg

**NBVC** · 11-21-2008, 05:45 PM

Not sure if it is needed in the Countif() solo formula... but in the Sumproduct()/Countif() formula the &"" you don't get a #DIV/0 error when there are blanks in your formula. The &"" essentially adds a null to each element in the array so when a blank appears, it is replaced with a null and the Countif counts that as 1 instead of 0.

The / is a divisor.. it divides the result of the Sumproduct() function by the result of the Countif() function. You don't want any 0's in the denominator or you will get that #DIV/0 error hence again the use of the &"" to concatenate a null to each cell (even if it is blank).

Hope that helps...

If you google a bit on unique counting, you find the person who first came up with that formula.. I think it probably was Harlon Grove... and his explanation...

To find out how these formulas work, go to Tools|Formula Auding|Evaluate Formula and keep clicking Evaluate button.. you will see the steps Excel takes to achieve the final result and how it does it.

**daddylonglegs** · 11-21-2008, 06:06 PM

HelloGreg,

See here for an explanation of the SUMPRODUCT formula

Amongst other things that explains why you need &"" in COUNTIF, it prevents a 0 being generated which would cause a #DIV/0 error. In your first formula you dont need &"" because you aren't dividing by anything.

So you can use just

=COUNTIF($C$1:$C$6000,$C$1:$C$6000)

copied down

[although if you keep &"" it will count blank cells rather than returning zero]

This formula returns an array. If you copy such a formula down the range then the displayed value is the one relevant to that row. I'd just use this formula copied down

=COUNTIF($C$1:$C$6000,$C1)

it'll give you the same results