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


"sabegirl" <[email protected]> wrote in
message news:[email protected]...
>
> 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.
>
>
> --
> sabegirl
> ------------------------------------------------------------------------
> sabegirl's Profile:
> http://www.excelforum.com/member.php...o&userid=33982
> View this thread: http://www.excelforum.com/showthread...hreadid=553000
>

[Mallycat]

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


Edit: Doh! beaten at the post

[Bluesky]

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

Hope it may help.

Best Regards
Bluesky

"sabegirl" wrote:

>
> 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.
>
>
> --
> sabegirl
> ------------------------------------------------------------------------
> sabegirl's Profile: http://www.excelforum.com/member.php...o&userid=33982
> View this thread: http://www.excelforum.com/showthread...hreadid=553000
>
>

[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