How many times is a name listed

[sabegirl]

Thanks for any help (but make it simple please)

I need to know how many times a item is listed.

A1 Bread
A2 Bread
A3 Bread
A4 Milk
A5 Sugar
A6 Sugar

How many times are bread, milk, and sugar listed? This will be for
finding out how many times inventory items were purchased.

 

[Nick Hodge]

You will do best to set up a list of the products and count the number
alongside there, but using your example in B1 enter

=COUNTIF($A$1:$A$6,A1)

and copy this formula down to B6. Obviously you will get the same result of
3 alongside each entry of bread for example, which is why I would set up a
separate table with unique items, but the theory is the same.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[Guest]

=COUNTIF(A:A,"Bread ")
=COUNTIF(A:A,"Milk ")
=COUNTIF(A:A,"Sugar ")

Hope it may help.

Best Regards
Bluesky

 

[Mallycat]

=COUNTIF(A1:A6,"bread") etc


Edit: Doh! beaten at the post

 

[VBA Noob]

Hi Sabegirl,


Try using Countif.

If you make a table like below you can refer to the cell reference
=COUNTIF($A$1:$A$6,C1)

C1 "Bread" D1 =COUNTIF($A$1:$A$6,C1)
C2 "Milk" D2 =COUNTIF($A$1:$A$6,C2)
C3 "Milk" D3=COUNTIF($A$1:$A$6,C3)

Note use F4 to add the $ so when you drag the formula down the range
stays absolute

otherwise just type in the word in a cell and replace the cell ref with
"Bread"

=COUNTIF($A$1:$A$6,"Bread")

Hope this helps

VBA Noob