need help finding string them averaging the total
 

I have two lists , column A & B , in column A is the # for the product€¦ i.e.:
A334GHLZ , in column C I have the price for that product..

Now, there is always more than 1 instance of the product # because this list
is referencing what each product sold to DIFFERENT customers for..

So say the list looks like this

___COL A_____ _____COL B_____
ASF1234GHLZ 19.00
ASF1234GHLZ 20.00
ASF1234GHLZ 35.00
ASF1234GHLZ 21.00
B3223424LHZ 5.0

(expect my list is now about 2,000 entries)

I need to be able to type €œASF1234GHLZ€� into a separate cell and I want
excel to find all instances on €œASF1234GHLZ€� & get there prices
(19.00,20.00,35.00,21.00) , then add the prices together and give me the
average of the prices. There may be 1 instance of a product, or there may be
20 instances of the product.

I obviously know how-to do the math part in excel, what I dont know is how
to make it search for that criteria and gather all of the prices from the
adjacent cells then add & divide them.



Any and all help you can give me is greatly appreciated!

Thanks,
Brandon Roland

need help finding string them averaging the total
 

=AVERAGE(IF(A1:A5=D1,B1:B5))

D1=€œASF1234GHLZ€�

Enter with Ctrl+Shift+Enter (array formula)

"brandon roland" wrote:

I have two lists , column A & B , in column A is the # for the product€¦ i.e.:
A334GHLZ , in column C I have the price for that product..

Now, there is always more than 1 instance of the product # because this list
is referencing what each product sold to DIFFERENT customers for..

So say the list looks like this

___COL A_____ _____COL B_____
ASF1234GHLZ 19.00
ASF1234GHLZ 20.00
ASF1234GHLZ 35.00
ASF1234GHLZ 21.00
B3223424LHZ 5.0

(expect my list is now about 2,000 entries)

I need to be able to type €œASF1234GHLZ€� into a separate cell and I want
excel to find all instances on €œASF1234GHLZ€� & get there prices
(19.00,20.00,35.00,21.00) , then add the prices together and give me the
average of the prices. There may be 1 instance of a product, or there may be
20 instances of the product.

I obviously know how-to do the math part in excel, what I dont know is how
to make it search for that criteria and gather all of the prices from the
adjacent cells then add & divide them.



Any and all help you can give me is greatly appreciated!

Thanks,
Brandon Roland

need help finding string them averaging the total
 

Hi,

Try,

=(SUMPRODUCT((A1:A500=E1)*(B1:B500))/COUNTIF(A1:A500,E1))

Where E1 is you search string

Mike

"brandon roland" wrote:

I have two lists , column A & B , in column A is the # for the product€¦ i.e.:
A334GHLZ , in column C I have the price for that product..

Now, there is always more than 1 instance of the product # because this list
is referencing what each product sold to DIFFERENT customers for..

So say the list looks like this

___COL A_____ _____COL B_____
ASF1234GHLZ 19.00
ASF1234GHLZ 20.00
ASF1234GHLZ 35.00
ASF1234GHLZ 21.00
B3223424LHZ 5.0

(expect my list is now about 2,000 entries)

I need to be able to type €œASF1234GHLZ€� into a separate cell and I want
excel to find all instances on €œASF1234GHLZ€� & get there prices
(19.00,20.00,35.00,21.00) , then add the prices together and give me the
average of the prices. There may be 1 instance of a product, or there may be
20 instances of the product.

I obviously know how-to do the math part in excel, what I dont know is how
to make it search for that criteria and gather all of the prices from the
adjacent cells then add & divide them.



Any and all help you can give me is greatly appreciated!

Thanks,
Brandon Roland

need help finding string them averaging the total
 

Toppers,
thanks for the quick reply.. it works except it is giving me the average for
all my cell entries in B1 - B5 , not only the average of cells that have
ASF1234GHLZ in them
say

COL A COL B
ASF1234GHLZ 1.00
ASF1234GHLZ 2.00
B12345DSFSF 3.00
DFS3453SDFD 2.00
ASF1234GHLZ 2.00

your code gives me the average of everything in column B , which in this
instance would be 1+2+3+2+2 = 10/5 = 2

I need it to only add the amount in B# if the string in A# is the same as
the query.. which would be 1+2+2 = 5/3 = 1.66667

Thanks though maybe you can still help me figure it out!


"Toppers" wrote:

=AVERAGE(IF(A1:A5=D1,B1:B5))

D1=€œASF1234GHLZ€�

Enter with Ctrl+Shift+Enter (array formula)

"brandon roland" wrote:

I have two lists , column A & B , in column A is the # for the product€¦ i.e.:
A334GHLZ , in column C I have the price for that product..

Now, there is always more than 1 instance of the product # because this list
is referencing what each product sold to DIFFERENT customers for..

So say the list looks like this

___COL A_____ _____COL B_____
ASF1234GHLZ 19.00
ASF1234GHLZ 20.00
ASF1234GHLZ 35.00
ASF1234GHLZ 21.00
B3223424LHZ 5.0

(expect my list is now about 2,000 entries)

I need to be able to type €œASF1234GHLZ€� into a separate cell and I want
excel to find all instances on €œASF1234GHLZ€� & get there prices
(19.00,20.00,35.00,21.00) , then add the prices together and give me the
average of the prices. There may be 1 instance of a product, or there may be
20 instances of the product.

I obviously know how-to do the math part in excel, what I dont know is how
to make it search for that criteria and gather all of the prices from the
adjacent cells then add & divide them.



Any and all help you can give me is greatly appreciated!

Thanks,
Brandon Roland

need help finding string them averaging the total
 

Mike that works!

Thanks bro! - BROLL

"Mike H" wrote:

Hi,

Try,

=(SUMPRODUCT((A1:A500=E1)*(B1:B500))/COUNTIF(A1:A500,E1))

Where E1 is you search string

Mike

"brandon roland" wrote:

I have two lists , column A & B , in column A is the # for the product€¦ i.e.:
A334GHLZ , in column C I have the price for that product..

Now, there is always more than 1 instance of the product # because this list
is referencing what each product sold to DIFFERENT customers for..

So say the list looks like this

___COL A_____ _____COL B_____
ASF1234GHLZ 19.00
ASF1234GHLZ 20.00
ASF1234GHLZ 35.00
ASF1234GHLZ 21.00
B3223424LHZ 5.0

(expect my list is now about 2,000 entries)

I need to be able to type €œASF1234GHLZ€� into a separate cell and I want
excel to find all instances on €œASF1234GHLZ€� & get there prices
(19.00,20.00,35.00,21.00) , then add the prices together and give me the
average of the prices. There may be 1 instance of a product, or there may be
20 instances of the product.

I obviously know how-to do the math part in excel, what I dont know is how
to make it search for that criteria and gather all of the prices from the
adjacent cells then add & divide them.



Any and all help you can give me is greatly appreciated!

Thanks,
Brandon Roland

need help finding string them averaging the total
 

Did you enter with Ctrl+Shift+Enter ... NO! so you get an answer of 2.

Do it correctly and you get the correct answer.


"brandon roland" wrote:

Toppers,
thanks for the quick reply.. it works except it is giving me the average for
all my cell entries in B1 - B5 , not only the average of cells that have
ASF1234GHLZ in them
say

COL A COL B
ASF1234GHLZ 1.00
ASF1234GHLZ 2.00
B12345DSFSF 3.00
DFS3453SDFD 2.00
ASF1234GHLZ 2.00

your code gives me the average of everything in column B , which in this
instance would be 1+2+3+2+2 = 10/5 = 2

I need it to only add the amount in B# if the string in A# is the same as
the query.. which would be 1+2+2 = 5/3 = 1.66667

Thanks though maybe you can still help me figure it out!


"Toppers" wrote:

=AVERAGE(IF(A1:A5=D1,B1:B5))

D1=€œASF1234GHLZ€�

Enter with Ctrl+Shift+Enter (array formula)

"brandon roland" wrote:

I have two lists , column A & B , in column A is the # for the product€¦ i.e.:
A334GHLZ , in column C I have the price for that product..

Now, there is always more than 1 instance of the product # because this list
is referencing what each product sold to DIFFERENT customers for..

So say the list looks like this

___COL A_____ _____COL B_____
ASF1234GHLZ 19.00
ASF1234GHLZ 20.00
ASF1234GHLZ 35.00
ASF1234GHLZ 21.00
B3223424LHZ 5.0

(expect my list is now about 2,000 entries)

I need to be able to type €œASF1234GHLZ€� into a separate cell and I want
excel to find all instances on €œASF1234GHLZ€� & get there prices
(19.00,20.00,35.00,21.00) , then add the prices together and give me the
average of the prices. There may be 1 instance of a product, or there may be
20 instances of the product.

I obviously know how-to do the math part in excel, what I dont know is how
to make it search for that criteria and gather all of the prices from the
adjacent cells then add & divide them.



Any and all help you can give me is greatly appreciated!

Thanks,
Brandon Roland