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

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
Joined
Apr 16, 2008
Messages
578
Sounds like you want SumProduct, =SUMPRODUCT(--(B4:B50="Name of Item"),--(J4:J50<>""))
 

Ted_Horsepower

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

ADVERTISEMENT

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

Ted_Horsepower

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

Ted_Horsepower

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

Forum statistics

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