need help finding string them averaging the total

[Guest]

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 don’t 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

 

[Guest]

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

D1=“ASF1234GHLZâ€

Enter with Ctrl+Shift+Enter (array formula)

 

[Guest]

Hi,

Try,

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

Where E1 is you search string

Mike

 

[Guest]

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!

 

[Guest]

Mike that works!

Thanks bro! - BROLL

Hi,

Try,

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

Where E1 is you search string

Mike

 

[Guest]

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

Do it correctly and you get the correct answer.