# Can SUMPRODUCT be used?

#### Craig1

##### Active Member
Hi Guys,
I hope I explain myself clearly enough here.

I want to use something like a SUMPRODUCT function to determine how may times a certain mechanical item appears on my list.
I can't put the item number in because I have a couple of thousand on the list.
If it can be done I want H1 to have the function inserted so it will show me the result and if I click on any cell in column H it will tell me how many times that cell value appears in column H. I want it to count all cells in the column, hidden or visible.
This is basically going to tell me if it is a "bad actor" and it needs further investigation and ultimately a replacement. Or is there another way of doing this.
I even though of a counter in culumn I, so when an item was repeated it had a continuous count up. So the first time it appeared on the list it had 1 in column I at the side of it and then 300 rows down it appears again so it has 2 in column I.

Any help would be appreciated or further explanation.

Craig.

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
For the formula in I2:

=COUNTIF(H\$2:H2,H2)

copied down.

Thanks for the quick reply Andrew.
That works perfectly.
Is there any chance you could explain how it works. I have come across and used a lot of functions but not COUNTIF, what does it exactly mean and do???

Thanks again

Craig.

It counts the number of cells within a range that meet the given criteria. For more information look in Excel Help.

Replies
9
Views
120
Replies
2
Views
163
Replies
2
Views
179
Replies
3
Views
367
Replies
3
Views
126

1,218,845
Messages
6,144,802
Members
450,567
Latest member
Mplz

### 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?

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