Formula needed
 

I need to be able to work out the percentage of certain orders placed by
certain groups.

In column A I have numbers which determines what kind of order it is, ie
3000??????? determines a catalogue order any other kind of number is either a
special request or service order. Column G determines what group made the
purchase. I have a list of all the groups which I would like the % for stored
in a named range 'Pgrps'.

I was thinkin of using sumproduct but I cant get the syntax right. Basically
the formula should look like

Sumproduct(range("A1:A5000"="3000")*Range("G1:G500 0"=Pgrps)/Count("A1:A5000")

Can anyone help me with this?

TIA

=SUMPRODUCT((A1:A5000=3000)*(ISNUMBER(MATCH
(G1:G5000,Pgrps,0))))/COUNT(A1:A5000)

If col. A is text, use "3000" and COUNTA in the formula

HTH
Jason
Atlanta, GA

-----Original Message-----
I need to be able to work out the percentage of certain
orders placed by
certain groups.

In column A I have numbers which determines what kind of
order it is, ie
3000??????? determines a catalogue order any other kind
of number is either a
special request or service order. Column G determines
what group made the
purchase. I have a list of all the groups which I would
like the % for stored
in a named range 'Pgrps'.

I was thinkin of using sumproduct but I cant get the
syntax right. Basically
the formula should look like

Sumproduct(range("A1:A5000"="3000")*Range
("G1:G5000"=Pgrps)/Count("A1:A5000")

Can anyone help me with this?

TIA
.

Hi

I have tried your example but I am still having problems. I should have
mentioned that as the report comes from our accounting system all values are
stored as text. I tried amending your formula to reflect this and also the
fact that the text in column A is 10 characters but I only need to test the
first 4.

=SUMPRODUCT((LEFT(A2:A18172,4)="3000")*(MATCH(G1:G 18172,PGrps,0)))/COUNTA(A2:A18172)

Can anyone help correct this?

TIA

"Jason Morin" wrote:

=SUMPRODUCT((A1:A5000=3000)*(ISNUMBER(MATCH
(G1:G5000,Pgrps,0))))/COUNT(A1:A5000)

If col. A is text, use "3000" and COUNTA in the formula

HTH
Jason
Atlanta, GA

-----Original Message-----
I need to be able to work out the percentage of certain
orders placed by
certain groups.

In column A I have numbers which determines what kind of
order it is, ie
3000??????? determines a catalogue order any other kind
of number is either a
special request or service order. Column G determines
what group made the
purchase. I have a list of all the groups which I would
like the % for stored
in a named range 'Pgrps'.

I was thinkin of using sumproduct but I cant get the
syntax right. Basically
the formula should look like

Sumproduct(range("A1:A5000"="3000")*Range
("G1:G5000"=Pgrps)/Count("A1:A5000")

Can anyone help me with this?

TIA
.

Edgar-

Your formula looks fine except you left out the ISNUMBER
portion of it.

Jason

-----Original Message-----
Hi

I have tried your example but I am still having
problems. I should have
mentioned that as the report comes from our accounting
system all values are
stored as text. I tried amending your formula to reflect
this and also the
fact that the text in column A is 10 characters but I
only need to test the
first 4.

=SUMPRODUCT((LEFT(A2:A18172,4)="3000")*(MATCH
(G1:G18172,PGrps,0)))/COUNTA(A2:A18172)

Can anyone help correct this?

TIA

"Jason Morin" wrote:

=SUMPRODUCT((A1:A5000=3000)*(ISNUMBER(MATCH
(G1:G5000,Pgrps,0))))/COUNT(A1:A5000)

If col. A is text, use "3000" and COUNTA in the formula

HTH
Jason
Atlanta, GA

-----Original Message-----
I need to be able to work out the percentage of
certain
orders placed by
certain groups.

In column A I have numbers which determines what kind
of
order it is, ie
3000??????? determines a catalogue order any other
kind
of number is either a
special request or service order. Column G determines
what group made the
purchase. I have a list of all the groups which I
would
like the % for stored
in a named range 'Pgrps'.

I was thinkin of using sumproduct but I cant get the
syntax right. Basically
the formula should look like

Sumproduct(range("A1:A5000"="3000")*Range
("G1:G5000"=Pgrps)/Count("A1:A5000")

Can anyone help me with this?

TIA
.


.