Help With Array Formulas...

[Carl]

Hello Group

I am trying to summarise a table of data I have in excel. The columns are :

Customer Account Number - is unique
Salesman - will give us multiple accounts, which each have a unique account
number
Profit - the profit made in each transaction

So the date might look like

Salesman Account Number Profit
Gary 00000001 300
Gary 00000002 400
Gary 00000003 200
John 00000004 300
John 00000005 100

I have defined each column with a name so I can do quick sums like
=sum(profit)

Using an array fomula I can sum (for example) Gary
{=sum((salesman="Gary")*(profit))}

However, I cant seem to tfind the function that would count how many deals
Gary had done!!! I am sure it must be simple, but I just cant seem to fin
it.

Any help apprecited.

Many thanks in advance.

Carl.

 

[JE McGimpsey]

One way:

=SUMPRODUCT(--(salesman="Gary"))

See

http://www.mcgimpsey.com/excel/doubleneg.html

for an explanation of the "--"

 

[Bob Phillips]

=COUNTIF(salesman,"Gary")

not an array formula

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Carl]

Thanks Bob! Thats fixed it ;o)

=COUNTIF(salesman,"Gary")

not an array formula

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

 

[Carl]

Couldnt get that to work, but have an answer sorted now! Thanks anyway!