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

[Ted_Horsepower]

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

 

[id107]

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

 

[Ted_Horsepower]

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]

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]

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

 

[Ted_Horsepower]

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

 

[T. Valko]

=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]

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)

 

[Ted_Horsepower]

Thanks, Biff ...that's helpful! :0)

 

[T. Valko]

Thanks, Biff ...that's helpful! :0)

You're welcome!