+ Reply to Thread
Results 1 to 9 of 9

How to get counts specific to data based on other entries in rows.

  1. #1
    Registered User
    Join Date
    06-22-2014
    Location
    Peoria, AZ.
    MS-Off Ver
    2016
    Posts
    27

    How to get counts specific to data based on other entries in rows.

    I am attaching an example spreadsheet that is not returning the result i am looking for. The formulas are listed in the areas needed. I want to accomplish 2 things. 1st - a count of the # of sales per dealership - got this achieved! What is not working for me is that i am looking to have a count of specific colors sold per dealership... somehow i am off.

    Any help would be hugely appreciated!
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    06-22-2014
    Location
    Peoria, AZ.
    MS-Off Ver
    2016
    Posts
    27

    Re: How to get counts specific to data based on other entries in rows.

    I attached the wrong spreadsheet...here is the correct one.
    Attached Files Attached Files

  3. #3
    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
    52,917

    Re: How to get counts specific to data based on other entries in rows.

    In both columns, you need to "lock" the starting cell, else it adjusts as you copy down...
    C2=IF(A2<>A1,COUNTIF($A$2:A38,A2),"")
    D2=IF(F2<>F1,COUNTIF($F$2:F38,F2),"")

    It was just luck that column C is giving the correct answers
    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

  4. #4
    Registered User
    Join Date
    06-22-2014
    Location
    Peoria, AZ.
    MS-Off Ver
    2016
    Posts
    27

    Re: How to get counts specific to data based on other entries in rows.

    Also, when i enter c2 or d2 befoere the formula, it does not recognize it as a formula... just text.

  5. #5
    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
    52,917

    Re: How to get counts specific to data based on other entries in rows.

    C2 and D2 are cell references. Those are the cells you would enter those formulas in

  6. #6
    Registered User
    Join Date
    06-22-2014
    Location
    Peoria, AZ.
    MS-Off Ver
    2016
    Posts
    27

    Re: How to get counts specific to data based on other entries in rows.

    Thanks Ford, but i still am experiencing a problem. The formula is returning the number of colors for the entire spreadsheet and not per Dealership? It also diminishes as you get down further on the spreadsheet as it is looking at less data. How can i achieve this?

  7. #7
    Registered User
    Join Date
    06-22-2014
    Location
    Peoria, AZ.
    MS-Off Ver
    2016
    Posts
    27

    Re: How to get counts specific to data based on other entries in rows.

    The result i am looking to achieve.... (without a formula in column D)is the formula of subset of the Dealerships i am looking for.

    Thanks All!
    Attached Files Attached Files
    Last edited by John M.; 12-16-2015 at 04:14 PM.

  8. #8
    Valued Forum Contributor
    Join Date
    10-13-2010
    Location
    Sunderland, England
    MS-Off Ver
    Excel 2007 (Home) / 2003 (Work)
    Posts
    740

    Re: How to get counts specific to data based on other entries in rows.

    =if(f2<>f1,countifs(a2:a38,a2,f2:f38,f2),"")

  9. #9
    Registered User
    Join Date
    12-15-2012
    Location
    In a bin
    MS-Off Ver
    Excel 2013
    Posts
    43

    Re: How to get counts specific to data based on other entries in rows.

    How about using pivot tables.
    Attached Files Attached Files

+ 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. Getting unique rows, counts of said rows and other data manipulation
    By ferdjones in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 12-16-2015, 01:54 PM
  2. Count rows then insert formula based on row counts
    By midoop in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-09-2013, 11:17 AM
  3. Enumeration of rows based on given counts
    By nilsonj in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-29-2013, 06:40 AM
  4. Formula/function that counts amount of rows between specific number.
    By angelitovs in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-12-2012, 09:29 PM
  5. [SOLVED] Sorting rows of data based off a single column for a variable number of entries.
    By soxcrates in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-28-2012, 12:31 PM
  6. Inserting Rows after Specific Entries
    By sigma1113 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-17-2011, 09:56 AM
  7. Deleting Rows Based on Specific Data
    By Jakz34 in forum Excel General
    Replies: 1
    Last Post: 04-12-2011, 09:00 PM
  8. counts rows in data
    By Bryan Brassell in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-02-2005, 06:00 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