Lookup on multiples

[runner]

I have a spreadsheet with location number in column A and product in column b and Sales in column C on sheet 1.

I have over 100 locations with and average of 300 products in each location so sheet one is over 30000 rows.

On sheet 2 I have location number in column A for all 100 locations, in column B I want a formula to calculate the % of products with sales in each location.

For example store 1234 has 100 products listed in column b, and 80 with sales listed in column C, so on sheet 2 store 1234 column b would be 80%.

Any ideas?

[oeldere]

Use pivot table for that kind of work.

[runner]

Can't use a pivot, well I could, but I plan to change store list, so the second sheet will be a forward using output sheet.

Pivots would have to constantly redo, reformat.

I am working on an array index match at the moment, should be able to have a formula for this, then I could just summarize instantly.

[runner]

Actually pivot want get me to where I need to be, a pivot would count, but would not take that count and give me a percentage of products selling in that location.

I basically have 100 stores with an average of 300 products.

Not all products will have sales, for example one store has 100 products with 80 products producing sales, pivot would give me the 80 by counting on the sales column, but I need an answer of 80%, not a count of 80.

I was thinking something like If(a1=a:a, count(c:c), countif(C:C>0)/count(c:c). But it has to be an array I believe because first you are looking a col A, and if the same then count column c>0 and divide by count of Column a1=a:a, a2=a:a, etc....

[oeldere]

Please Login or Register  to view this content.

Yes, a pivot could do that for you.

Please Login or Register  to view this content.

I think that would be possible with an pivot table.

Please Login or Register  to view this content.

I think, (I'm not 100% sure), that count which such large file, for all rows (> 1 mln) tremendous slow the performance of your file.

[runner]

I can create 2 formulas one to count the number of times the store number appears in column A and count itmes >0 in sales column. Should be able to combine the 2

[kvsrinivasamurthy]

Pl see the attched file.

[oeldere]

See the attached file with an pivot table.

[runner]

Thank you, this is exactly what I was looking for but with one tweak:

I need the formula to return as below - sh123 has 3 of 4 products with sales so 75% is what I am looking for, and sh124 has 1 of 4 products with sales so 25% is what I am looking for.


shop product sales
sh123 p1 y
sh123 p1 y
sh123 p3 y
sh123 p3
sh124 p2 y
sh124 p2
sh124 p4
sh124 p4


shop Need
sh123 100% 75% 3 of 4 products have sales
sh124 25% 1 of 4 products have sales
sh125
sh126
sh127 0%
sh128

[runner]

Thank you, this is exactly what I was looking for but with one tweak:

I need the formula to return as below - sh123 has 3 of 4 products with sales so 75% is what I am looking for, and sh124 has 1 of 4 products with sales so 25% is what I am looking for.


shop product sales
sh123 p1 1
sh123 p1 2
sh123 p3 4
sh123 p3 0
sh124 p2 10
sh124 p2 0
sh124 p4 0
sh124 p4 0


shop Need
sh123 100% 75% 3 of 4 products have sales
sh124 25% 1 of 4 products have sales
sh125
sh126
sh127 0%
sh128

[runner]

On my file Column A is where the location resides, column G is where the sales reside, if a store sales 80 out of 100 of its products, I want the formula to return 80%. The above formula does not work, I want all products in G greater than 0 divided by all products in g when the store number on sheet 2 matches sheet 1.

[oeldere]

Is the file not tremendeous slow with your formula?

I'm interested in your respond on this question.

If you like the idea of my pivot table, you could try it solve that way.

[runner]

Not slow, even with that amount of rows, I did the count if in about 30 seconds. That what excel does right? Confused by the response? I thought excel 2007 was set to go to over 1.5 million cells, so why worry about a 300k cel spreadsheet. Please explain, not following you? I did a count if on cel a with 300k, and have done numerouse formulas on spreadsheet, all is fine ?

[runner]

I will repost my question in a different way later with a spreadsheet sample or the exact wb, I may have not asked the question correctly and you seem confused with the answer you gave not giving me the result I am looking for, thanks,

[oeldere]

Thanks for your reply in #13.

I realy appreciate your reply.

If you post another file, I will take a look at it.

[runner]

Thank you sir for the pivot recommendation, I can use it elsewhere. I was basically looking for a formula to sum up the % of products sold for about 3000 locations, I got the formula I desired:

=IFERROR(SUMPRODUCT(--(Sheet1!$A$2:$A$952=Sheet2!$A2),--(Sheet1!$G$2:$G$952>0))/SUMPRODUCT(--(Sheet1!$A$2:$A$952=Sheet2!$A2)),"")

Basically my original formula logic was flawed in that I did not realize the basic sumproduct of A:A=A2 calculated the total products, I thought I had to add a code for column G.

Thanks again for your assistance and if I get stumped again, I will call on you.