+ Reply to Thread
Results 1 to 12 of 12

[SOLVED] Count the amount of times specific data appears based on criter

  1. #1
    Registered User
    Join Date
    04-11-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    16

    [SOLVED] Count the amount of times specific data appears based on criter

    Hi all - is anybody able to help me with what is probably a simple fix....
    i am looking for a formula to.....

    if a cell in column A10:A100="YP45869" count how many times "RBO Shirts" appears in column B10:B100

    Many thanks in advance
    Last edited by Ash Ford; 04-11-2013 at 12:04 PM. Reason: Sloved

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: excel 2003 formula

    =SUMPRODUCT(--($A$2:$A$11="YP45869"),--($B$2:$B$11="RBO Shirts"))


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    04-11-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: excel 2003 formula

    Hi TMS - many thanks

    i still cannot get a result - the result cell which should equal 1 remains blank?

    YP45869 RBF 97
    YM56789 QPF 56
    YP45869 RBO Shirts
    YP45869 RBF 108

    any ideas ??

  4. #4
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: excel 2003 formula

    Ash,

    Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem. Once you have done this please send me a PM and I will remove this request.

    To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  5. #5
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: excel 2003 formula

    Quote Originally Posted by Ash Ford View Post
    Hi TMS - many thanks

    i still cannot get a result - the result cell which should equal 1 remains blank?
    Then your data is either NOT "YP45869" or NOT "RBO Shirts" (though it may look like it)
    Spaces in the data maybe?

    Post your spreadsheet
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  6. #6
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: excel 2003 formula

    Special-K,

    Sorry about it, but i guess you didnt see post 4.

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: count the amount of times specific data appears based on criteria from anoth column

    Thank you for complying with the forum rules and changing your thread title. Interestingly, your new title is in some ways less descriptive than the old one in that you specifically want a formula for Excel 2003.

    If you had a later version of Excel, you could use COUNTIFS rather than SUMPRODUCT.

    That said, Special-K is right ... your data is not as it seems. It must have spaces or non printing characters that prevent the match. The formula could be modified to use LEFT and/or MID but it would be better to understand your data.

    I suggest that you post a sample workbook for us to assess.

    Regards, TMS

  8. #8
    Registered User
    Join Date
    04-11-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: excel 2003 formula

    Hi Special K

    showing my true colours now - i cannot seem to attach the spreadsheet!!
    Attached Files Attached Files
    Last edited by Ash Ford; 04-11-2013 at 07:35 AM.

  9. #9
    Registered User
    Join Date
    04-11-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: excel 2003 formula

    Hi all please now find attached the spreadsheet

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: Count the amount of times specific data appears based on criteria from another column

    C2: =SUMPRODUCT(--($B$7:$B$12=$A2),--($C$7:$C$12=C$1))

    You have trailing spaces on the lists in column A and column B. The above formula will work if you sort out your data.


    Regards, TMS

  11. #11
    Registered User
    Join Date
    04-11-2013
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    16

    Re: Count the amount of times specific data appears based on criteria from another column

    Quote Originally Posted by TMShucks View Post
    C2: =SUMPRODUCT(--($B$7:$B$12=$A2),--($C$7:$C$12=C$1))

    You have trailing spaces on the lists in column A and column B. The above formula will work if you sort out your data.


    Regards, TMS
    Many thaks TMS - great result!!

  12. #12
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: [SOLVED] Count the amount of times specific data appears based on criter

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1