Count Formula-Would this be a good example to use the count function?

**JK1234** · 08-01-2008, 11:43 AM

Would this be a good example to use the count function??? if so could you get me started??!!!!

I have a list that have the same items in it and all I want to do is only show them once but to add up the values in each.

Never used it and playing with the idea to replace a pivot table

**VBA Noob** · 08-01-2008, 11:46 AM

Try

=SUMIF($B$9:$B$18,B22,$C$9:$C$18)

VBA Noob

**JK1234** · 08-01-2008, 11:52 AM

Works great!!! I was never thought about sumif......I have just learning better ways to simplify my formulas - moving away from macros and VB that I am actually better at, but finding out Excel has its own formulas that seem to be also work!!!

Thanks!

**JK1234** · 08-01-2008, 12:48 PM

QUESTION: How do I get B22:B26 to only show one instance of the items

**VBA Noob** · 08-01-2008, 12:51 PM

See if link helps

http://www.contextures.com/xladvfilter01.html#FilterUR

VBA Noob

**JK1234** · 08-01-2008, 01:17 PM

I got it to work nicely. I was wondering how would I get it to work if I add a different item or change the name of the Items. I tried it and it doesn't work that way???

**VBA Noob** · 08-01-2008, 01:34 PM

You would need an array formula - Enter with ctrl + Shift + Enter

=INDEX($A$1:$A$10,SMALL(IF(ROW($A$1:$A$10)=MATCH($A$1:$A$10,$A$1:$A$10,0),ROW($A$1:$A$10)),ROW()))

or code

VBA Noob

**JK1234** · 08-01-2008, 02:18 PM

I think you lost me here....???

DO I have to move the data..I assume this is an example of how to use the code. ???

**VBA Noob** · 08-01-2008, 02:21 PM

Change the range to your range of items. Then commit with Ctrl + shift + enter

then drag down...this will give you a list of all unqiue items in the range. That is what you wanted

VBA Noob