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

corbie82

New Member
Joined
Jun 7, 2014
Messages
23
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 2nd 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 2nd 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
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

corbie82

New Member
Joined
Jun 7, 2014
Messages
23
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 ?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,737
Messages
5,597,826
Members
414,180
Latest member
Sir Khaya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top