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

Ted_Horsepower

New Member
Joined
May 21, 2011
Messages
8
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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sounds like you want SumProduct, =SUMPRODUCT(--(B4:B50="Name of Item"),--(J4:J50<>""))
 
Upvote 0
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?
 
Upvote 0
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 -->
 
Upvote 0
=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:
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

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
Back
Top