Results 1 to 9 of 9

Thread: VBA to count
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to count

    c m
    brand 1 2
    brand 4 1
    brand 4 1
    brand 1 3
    brand 7 1


    I need to count quantity of the brands , there are many brands numbers in the list.using new excel.
    The result should be brand 1 = 5 brand 4 = 2 brand 7 = 1

    Thanks !

  2. #2
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    648
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

    Default Re: VBA to count

    Hey,

    =SUMPRODUCT(($C$2:$C$6="brand 1")*($M$2:$M$6))

    Assuming brand names in Col C (rows 2 - 6) & the quantity in Col M (rows 2 - 6)

    NB: You can change "brand 1" to be more dynamic by making a cell as "brand 1" and referencing the cell.
    √-1 2³ ∑ π
    …And it was delicious!

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,201
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA to count

    Another way
    =SUMIF(C:C,"Brand1",M:M)
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    New Member
    Join Date
    Jul 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to count

    Quote Originally Posted by tyija1995 View Post
    Hey,

    =SUMPRODUCT(($C$2:$C$6="brand 1")*($M$2:$M$6))

    Assuming brand names in Col C (rows 2 - 6) & the quantity in Col M (rows 2 - 6)

    NB: You can change "brand 1" to be more dynamic by making a cell as "brand 1" and referencing the cell.

    I got error value.....
    There are many brands i want to count isn't it possible not to type the brands ?

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,201
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA to count

    Like

    CDEFGHIJKLMNOP
    2cm
    3brand 12brand 15
    4brand 41brand 42
    5brand 41brand 71
    6brand 13
    7brand 71

    Sheet1



    Worksheet Formulas
    CellFormula
    P3=SUMIF(C:C,O3,M:M)

    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    New Member
    Join Date
    Jul 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to count

    Quote Originally Posted by Fluff View Post
    Like

    C D E F G H I J K L M N O P
    2 c m
    3 brand 1 2 brand 1 5
    4 brand 4 1 brand 4 2
    5 brand 4 1 brand 7 1
    6 brand 1 3
    7 brand 7 1
    Sheet1

    Worksheet Formulas
    Cell Formula
    P3 =SUMIF(C:C,O3,M:M)
    Yes that's what i need but i get results of 0

  7. #7
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    648
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

    Default Re: VBA to count

    Are the quantities stored as Numbers?

    It may be the case that they are Text or prefixed with an apostrophe - in which the formula @Fluff wrote will return 0 as you have said has happened
    √-1 2³ ∑ π
    …And it was delicious!

  8. #8
    New Member
    Join Date
    Jul 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to count

    Quote Originally Posted by tyija1995 View Post
    Are the quantities stored as Numbers?

    It may be the case that they are Text or prefixed with an apostrophe - in which the formula @Fluff wrote will return 0 as you have said has happened
    The quantity are numbers how can i solve this?

  9. #9
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,794
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: VBA to count

    Also make sure that your entries in columns C and O match EXACTLY.
    Any extras spaces in one and not the other will cause it NOT to match, and return 0s.
    Last edited by Joe4; Jul 26th, 2019 at 09:34 AM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •