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
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

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
:) 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)
 

Watch MrExcel Video

Forum statistics

Threads
1,101,935
Messages
5,483,776
Members
407,410
Latest member
catherinejoy

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top