Count of Engagements by Salesperson

[ogerriz]

I have a table that 1240 rows and 300 columns. The main column field that we
use to summarise the data via pivot tables is the engagement column. The
eng# is unique however it can appear in that column more than once because
there are services within that engagement and that is also in the table.

How can I count the number of engagements but not the number of entries in
the table (if I say count then it gives me 1240 obviously).

A sample will be:

Eng # EMID Service # Salesperson
12A 1 57477 John smith
12A 1 57485 John Smith
12A 1 60000 John Smith
13A 50 57486 Joe blow
14A 5 57999 John Smith

so if i ask for the count of engagements the result I want to get is 3

but if I ask for a count of engagements for "John Smith" then the result i
want Several people have already replied to my previous post with the formulas

If you have no blank cells in your data. The formula can be
simplified

=SUMPRODUCT(1/COUNTIF(A2:A6,A2:A6))
or
=SUMPRODUCT((A2:A1240<>"")/COUNTIF(A2:A1240,A2:A1240&""))

but i can't seem to understand what the functions are doing to work out how
to create a different formula for this question.

Could you also please explain a bit coz the Excel help function blog doesn't
really explain why this formula works?

thanks

 

[Jacob Skaria]

Refer the below post for an explanasion by Biff of how SUMPRODUCT() works to
count the number of distinct items...


http://www.microsoft.com/communitie...c6db&mid=73e0569e-ade9-4287-8a2d-0b23b77bc6db


If this post helps click Yes