# Counting only occurences of text item in a list that has a value entered against it in the same row

#### Ted_Horsepower

##### New Member
MS Excel 2007, Vista,

The task asks that I find the item of (in this case) second hand uniform that has sold most units.
1. To do so I need to create a list of unique items (text) found in a column that list all individual items that are or have been in stock. (which I can do using Advanced filter)
2. I need to then count the occurences of each unique item that has actually been sold (the range I need to scan B4:B50)
3. The only indication that an item has been sold is that there is a value (selling price) entered in a column (J4:50)
4. I can sort of achieve this by filtering and copying and pasting and countif but I then have to enter each countif range individually becuase it transposes the range making it invalid)
5. I am sure that there is a relatively simple way to do this but I am quite the novice...
Any help much appreciated

Ted

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### id107

##### Well-known Member
Sounds like you want SumProduct, =SUMPRODUCT(--(B4:B50="Name of Item"),--(J4:J50<>""))

#### Ted_Horsepower

##### New Member
Thanks, idio7, that neatly does it. Only problem I have (and I am sure that it is simple) Is there a way that I can copy the formula without it constantly transposing/shiftig the range down one each time I replicate it down a list. Ideally it will transpose the the cell reference for the item but not the range - if that makes sense?

#### id107

##### Well-known Member
If you put a \$ infront of any row/column, it will anchor that reference

=SUMPRODUCT(--(\$B\$4:\$B\$50="Name of Item"),--(\$J\$4:\$J\$50<>"")) <!-- / message --><!-- sig -->

#### Ted_Horsepower

##### New Member

That's great! Much appreciated - as they say " It's easy when you know how!"

#### Ted_Horsepower

##### New Member
=SUMPRODUCT(--(\$B\$4:\$B\$50=E61),--(J\$4:J\$50<>""))

Why does this formula have double dashes between the brackets? I understand the rest not this bit. The formual also seems to work with single dashes. Is this just a convention or soemthing else?

Last edited:

#### T. Valko

##### Well-known Member

=SUMPRODUCT(--(\$B\$4:\$B\$50=E61),--(J\$4:J\$50<>""))

Why does this formula have double dashes between the brackets? I understand the rest not this bit. The formual also seems to work with single dashes. Is this just a convention or soemthing else?
Take a look at this:

http://xldynamic.com/source/xld.SUMPRODUCT.html

#### Ted_Horsepower

##### New Member
Oh wait (replies to himself!) A little research suggests that it is used to convert the string into a number and back again so that it can be counted by the SUM PRODUCT...i think thats it...roughly speaking :0)

Replies
0
Views
262
Replies
4
Views
430
Replies
5
Views
275
Replies
3
Views
336
Legacy 456155
L
Replies
7
Views
908

1,141,299
Messages
5,705,571
Members
421,399
Latest member
hjweiss00

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

### Which adblocker are you using?

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

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