Help With Array Formulas...
 

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.

Help With Array Formulas...
 

One way:

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

See

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

for an explanation of the "--"

In article ,
"Carl" wrote:

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.

Help With Array Formulas...
 

=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" wrote in message
...
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.

Help With Array Formulas...
 

Thanks Bob! Thats fixed it ;o)

"Bob Phillips" wrote in message
...
=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" wrote in message
...
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.

Help With Array Formulas...
 

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

"JE McGimpsey" wrote in message
...
One way:

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

See

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

for an explanation of the "--"

In article ,
"Carl" wrote:

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.

Hi Carl

Assuming the salespersons name is in column A try =COUNTIF(A*:A*,"Gary"), replacing * with the cell references

Paul

Custom Toolbars - keep dissappearing
 

Re-build you toolbar then don't open files with macros enabled in the future.

Toolbar and menu customizations are stored in Excel9.xlb(2000 version)

Save a copy of that file so's you can restore if it gets changed.

Best method of assigning macros to buttons is to build the Toolbar when you open
the workbook and delete it when you close.

Do not make the changes to the existing toolbars.

See Debra Dalgleish's website for Dave Peterson help on building "on the fly"
toolbars and menus.

http://www.contextures.on.ca/xlToolbar02.html


Gord Dibben MS Excel MVP




On Mon, 12 Feb 2007 14:56:43 +0000, Paul S
wrote:


I am using excel 2000

I recently created a custom toolbar with 4 custom buttons but now
cannot find the toolbar

I think this was caused by opening a file someone sent to me, which has
re-set all my toolbars

How can I stop this happening

Paul