+ Reply to Thread
Results 1 to 9 of 9

count distinct values and categories

  1. #1
    Registered User
    Join Date
    11-15-2020
    Location
    dubai
    MS-Off Ver
    2019
    Posts
    8

    count distinct values and categories

    I am categorizing taxis in RED,YELLOW,WHITE,GREEN.
    I want to count the taxi in each cateogry which may be of type goka,casanova,orbit.

    I have attached my sheet.
    It has taxis in different cities. They fall in various categories.
    I have to count the number of distinct cities in excel.

    Number of taxis in each city that falls in category Red,Green,White,Yellow.
    Also number of gokia,orbit, casanova taxi which may fall in different different
    category.
    Total number of taxi in each category that is marked Red, Green,Yellow, White category.
    Taxi types are gokia,orbit and casanova.
    gokia ,casanova fall in category 1,
    orbit is in category 2.
    Attached Files Attached Files
    Last edited by poaxxafllad; 11-16-2020 at 12:54 AM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    27,128

    Re: taxi counting in sheet

    I do not understand. You have several sheets and have given us no indication where we should be looking... and what you expect to see. Please amend your sample sheet. Add some manually calculated expected results, in the place where you expect to see them.

    Also, please amend your location to show your approximate geograpical location (e.g. country). This may be important in selecting the correct formula.

    APVU - Asia Pacific Vegan Union? - Assisted Prison Visits Unit?????
    Glenn



  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,175

    Re: taxi counting in sheet

    Astra Projection Virtual Universe
    ChemistB
    My 2˘

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Registered User
    Join Date
    11-15-2020
    Location
    dubai
    MS-Off Ver
    2019
    Posts
    8

    Re: taxi counting in sheet

    The sample result for each sheet {all taxi,category 1,category 2}
    should look like this
    Red 45
    Yellow 34
    White 12
    Green 440
    Data missing
    Total 1440



    This sample result is applicable to all sheets in workbook
    all taxi, category 1,category 2.
    i.e. at the last line of each sheet I have to write this result in each sheet.

    For my own understanding and to find out which city is mentioned
    I have made total number of cities sheet in workbook.
    The row numbers in total number of
    cities sheet written in columns C,D, are row numbers from all taxi sheet.
    If this can also be automated in counting i.e. number of distinct values of column A from all taxi sheet to count only distinct values
    there are multiple repeated values in column A of all taxi sheet. Counting this and putting in total cities sheet.
    Currently I have manually counted in total number of cities
    sheet.

    Sheet total number of cities in workbook has entries which correspond to entries in all taxi sheet.
    I have counted the cities in all taxi sheet and noted the values in total number of cities in following format.
    city starting row in all taxi sheet ending row all taxi sheet difference
    location 1 A3 A96 93
    wuhan A97 A207 110
    Shenzen A250 A322 72
    Guangzhou A323 A365 42
    Tianjin A366 A432 66
    Nanjing A433 A513 80
    Chengdu A514 A559 45
    Hangzhou A560 A593 33
    Urumqi A594 A645 51
    Since there are a lot of cities
    for example in sheet1 for entries in rows between
    A1704 to A1782 for city Hohot sample results are in cells between I1704 to M1704 and I1719 to M1719.

    Sample result for each city for sheet 1 are in column I to N in sheet1.

    Like this it has multiple sample results for each city. You can see results for Shenzen
    in I253 to I268 and M253 to M268.
    Similarly for each city it has results.

    Then in category1 sheet
    there are 1760 rows and take columns A to F in consideration.
    The idea is taxi gokia and casanova have different fuel average.For gokia
    a taxi having fuel average
    A B C D E
    city taxi type(gokia) range color quantity
    Shenzen gokia 5-7 Red 0
    Shenzen gokia 7-8 Yellow 0
    Shenzen gokia 8-9 White 0
    Shenzen gokia 9> Green 6
    now for taxi casanova in Shenzen city sample result is following type
    A B C D E
    city taxi type(casanova) range color quantity
    Shenzen casanova 5-8 Red 0
    Shenzen casanova 8-9 Yellow 0
    Shenzen casanova 9-10 White 0
    Shenzen casanova 10> Green 6
    for a taxi having fuel average in boundary case i.e. 7 it should go to previous range. i.e. for a
    vehicle type gokia if average is 7 then it should go to range 5-7
    and not 7-8.
    For taxi type orbit
    A B C D E
    city taxi type(orbit) range color quantity
    Shenzen orbit 20-30 Red 0
    Shenzen orbit 30-33 Yellow 0
    Shenzen orbit 33-36 White 0
    Shenzen orbit 36> Green 6
    For vehicle type of gokia and casanova for each city from all taxi sheet have been put in category 1 sheet.
    Vehicle type orbit from all taxi sheet have been put in category2 sheet.
    Last edited by poaxxafllad; 11-16-2020 at 01:21 AM.

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    6,987

    Re: taxi counting in sheet

    Does colour put manually? if it does, move this thread to VBA section in oder to get properly answer.
    Formula can not read colour format.

  6. #6
    Registered User
    Join Date
    11-15-2020
    Location
    dubai
    MS-Off Ver
    2019
    Posts
    8

    Re: taxi counting in sheet

    yes coloring is done manually if there is some automatic way to do so I want to know.
    Coloring is done based on criteria in columns I to N in all taxi sheet.

    I used a formula on All taxi sheet
    Please Login or Register  to view this content.
    as described here got-it.ai/solutions/excel-chat/excel-tutorial/count/count-unique-values-excel
    but this gives divide by zero error
    Last edited by poaxxafllad; 11-16-2020 at 10:30 PM.

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,177

    Re: count distinct values and categories

    As there are blank cells in the range A3:A2547 try the following array entered formula**:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
    Let us know if you have any quesitons.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  8. #8
    Registered User
    Join Date
    11-15-2020
    Location
    dubai
    MS-Off Ver
    2019
    Posts
    8

    Re: count distinct values and categories

    Ok your suggestion has worked. I was able to get 40. Pressing Control then shift then enter was something I did not knew.
    Now I am trying to clear the colors of excel of column F from All taxi sheet. So that I can post here and explain the problem.
    I had put my cursor on topmost of column F there became a downward arrow sign and when I selected clear formatting I got a popup
    saying that we can't do that to a merged cell.
    So I started manually deleting the formatting of column F. I reached F1132 hold down shift key and down arrow key
    row number 1136 is having merged cells so this makes the work difficult as I can not select the entire column now.
    So is there a solution for this situation so that I can clear the formatting of entire column.

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,177

    Re: count distinct values and categories

    I suggest starting with F1132 and using Ctrl, Shift and down arrow until everything down to row 2545 is selected and then select to unmerge cells.
    Next Start with cell F3 and again use Ctrl, Shift and down arrow to select all cells down to F2545 at which point you can clear the formatting.
    Let us know if you have any questions.
    Last edited by JeteMc; 11-29-2020 at 08:53 PM. Reason: edited text

+ 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. taxi fare equation help
    By ericb2021 in forum Excel General
    Replies: 3
    Last Post: 10-25-2020, 03:35 AM
  2. [SOLVED] How to use INDEX+MATCH? Taxi question
    By onewishtobegranted in forum Excel General
    Replies: 11
    Last Post: 08-28-2020, 10:13 AM
  3. Using excel to calculate taxi fares
    By ineedhelpguys in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-21-2020, 03:37 AM
  4. Formula needed for a Taxi booking log
    By RAJIV5B in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-13-2016, 06:38 AM
  5. Check if a taxi was used or not from the list of rides
    By chrisvercser in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-30-2015, 08:25 AM
  6. Mileage tariffs for a taxi
    By tomkilbourn in forum Excel General
    Replies: 21
    Last Post: 07-23-2009, 10:10 AM
  7. [SOLVED] taxi driver accounts template
    By Dave in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 04-06-2006, 07:25 AM

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