showing the total number of products not just if they have it

[corbie82]

HI All

I regret that In my job I don’t get to do as much work with reports as I would like as I just keep forgetting some of the basic stuff never mind the more complex formula’s and strategies.

I have a sheet called raw data that shows the company name and products that our customers have. For each different product they have they will have one row and some will have 1 row and some will have 10 etc. On the 2^nd sheet I copied over all the companies name and using count if formula was able to show all the companies and if they had a product or not with us that was easy and straight forward.

However in sheet 1 there is another column that mentions quantity so some of them will have 4 products or 5 and some just 1 etc. How do I get that to show properly in the 2^nd sheet the total number of the products and not just show that they have a product or not I know it going to be straight forward but just cant seem to work it out.

This is the count if formula I use
=COUNTIFS('Export Worksheet'!H:H,SAVINGS!$H$1,'Export Worksheet'!A:A,SAVINGS!A2)
So Worksheet H is the name of the product and Savings H1 is the same name and A:A is the name of the companies and A2 is the name of the company

 

[corbie82]

So i found a way to do waht i need it to do but it seems to fail on slightly
=INDEX($A$1:$J$10000,MATCH(1,(A:A=Q2)*(H:H=$X$1),0),10)

if i use and index and match formula it does work but i either have to go through all the cells which is around 2.5k and click ctrl shift and enter to get it to show the details if not it will show n/A i then tried to highlight the entire column and do but it overites all the cells and makes them the same numbers . Any ideas ? also instead of iit saying #N/A can i get it just say 0 ?