Count of Engagements by Salesperson

O

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top