+ Reply to Thread
Results 1 to 11 of 11

Count number of time it appears in the data list

  1. #1
    Registered User
    Join Date
    07-01-2015
    Location
    Singapore
    MS-Off Ver
    2007
    Posts
    51

    Count number of time it appears in the data list

    Hi, I have a sheet with a list of potential buyer who have viewed the car. What formula should I used to count the number of times a potential buyer has viewedthe vehicle during a specific month and year and the number of view will be reflected on another sheet? I have tried using sumproduct but I'm having error. I am unsure if I have used the formula wrongly.

    Hope to seek some advice on this.

    Thank You
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Count number of time it appears in the data list

    Try this, copied down and across...
    =COUNTIFS(Statistics!B:B,"*"&D$2&"*",Statistics!C:C,">="&$C3,Statistics!C:C,"<"&EDATE($C3,1))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Count number of time it appears in the data list

    Using what you started with put this in D3 and enter it as an array formula (control + **** + enter) and drag across and down

    Please Login or Register  to view this content.
    Happy with my advice? Click on the * reputation button below

  4. #4
    Registered User
    Join Date
    07-01-2015
    Location
    Singapore
    MS-Off Ver
    2007
    Posts
    51

    Re: Count number of time it appears in the data list

    Thanks FDibbins and Crooza for the prompt reply. I have tried both formula using the steps given however, the results dosent tally with the Statistics

  5. #5
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Count number of time it appears in the data list

    Hmmm. I did a very quick check of mine and the ones I checked matched my expected outcome. What is your expected outcome and where does it differ? Did you enter the formula as an array formula?

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Count number of time it appears in the data list

    Apologies, I neglected to fix some ranges...
    =COUNTIFS(Statistics!$B:$B,"*"&D$2&"*",Statistics!$C:$C,">="&$C3,Statistics!$C:$C,"<"&EDATE($C3,1))
    copied down and across

  7. #7
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Count number of time it appears in the data list

    And here's the file. As I said above it looks right to me but let me know where you expected difference

    Edit - I also just checked Ford's solution and we're getting the same results
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    07-01-2015
    Location
    Singapore
    MS-Off Ver
    2007
    Posts
    51

    Re: Count number of time it appears in the data list

    Quote Originally Posted by Crooza View Post
    Hmmm. I did a very quick check of mine and the ones I checked matched my expected outcome. What is your expected outcome and where does it differ? Did you enter the formula as an array formula?
    Crooza, apologise, I accidentally press onto one letter that's wht it's not tally. Apologise for my mistake..

  9. #9
    Registered User
    Join Date
    07-01-2015
    Location
    Singapore
    MS-Off Ver
    2007
    Posts
    51

    Re: Count number of time it appears in the data list

    Quote Originally Posted by FDibbins View Post
    Apologies, I neglected to fix some ranges...
    =COUNTIFS(Statistics!$B:$B,"*"&D$2&"*",Statistics!$C:$C,">="&$C3,Statistics!$C:$C,"<"&EDATE($C3,1))
    copied down and across
    Thanks FDibbins, your formula works as well..

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Count number of time it appears in the data list

    Good stuff. happy to help and thanks for the feedback

  11. #11
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Count number of time it appears in the data list

    great. glad it worked

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Trying to find number of times an ID number appears in a data list
    By RohanF in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-10-2015, 09:12 PM
  2. How do i count the number of times a sequence appears in a row of data
    By dony85 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-02-2014, 01:57 PM
  3. Replies: 6
    Last Post: 11-26-2014, 10:35 PM
  4. Replies: 3
    Last Post: 04-26-2013, 01:21 PM
  5. Replies: 5
    Last Post: 01-08-2013, 02:53 PM
  6. count number of time a text string appears
    By wolfpack95 in forum Excel General
    Replies: 14
    Last Post: 12-20-2009, 02:57 PM
  7. [SOLVED] count the number of times data appears on the same row from two c.
    By GavT9 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-01-2005, 09:06 PM

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