How many times is a name listed

S

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

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
[email protected]
 
B

Bluesky

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

Hope it may help.

Best Regards
Bluesky
 
V

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
 
Top