# count distinct values and categories

1. ## 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.

2. ## 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?????

3. ## Re: taxi counting in sheet

Astra Projection Virtual Universe

4. ## 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.

5. ## 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. ## 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

7. ## Re: count distinct values and categories

As there are blank cells in the range A3:A2547 try the following array entered formula**:
Formula:
`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.

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. ## 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.

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

#### 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