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.

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

Astra Projection Virtual Universe

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.

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.

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

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.

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.