In cell H2, get the unique Area values using UNIQUE(). In column I, use COUNTIF() to get the count of each Area. Pass the data range from A2 to A45 as absolute addresses, and the filter criteria from H2 to H9. In column J, use COUNTIFS() to get the count of each area with more than 20 Swallow-tails. the first two arguments are the same as the previous step, then add a condition for column D to be greater than 20. Area Locality Skipper Swallowtail White-yellow Blue Brush-footed Area Count of Area Count of Area with 20 Swallowtails Indian Subcontinent Indian Subcontinent 307 94 99 458 482 Western Himalaya Western Himalaya 63 31 42 129 152 Western Himalaya Kangra Hills 25 23 37 56 87 Western Himalaya Shimla Hills 41 21 34 88 115 Western Himalaya Dehradun Valley 22 11 19 42 54 Western Himalaya Mussoorie Hills 54 23 32 88 126 Western Himalaya Mussoorie Town 14 10 13 44 65 Western Himalaya Kumaon Hills 52 26 37 109 147 Central Himalaya Central Himalaya 125 43 49 185 221 North East India + North Myanmar North East India + North Myanmar 211 69 57 284 342 North East India + North Myanmar North East India 189 62 52 258 292 North East India + North Myanmar Sikkim 159 55 51 162 263 North East India + North Myanmar Darjeeling 27 29 32 48 126 North East India + North Myanmar Naga Hills 67 38 30 110 178 North East India + North Myanmar Manipur 119 18 1 126 57 North East India + North Myanmar Khasia Hills 98 42 36 128 207 North East India + North Myanmar Khasia + Jaintia Hills 132 49 40 166 209 North East India + North Myanmar Mizoram Hills 59 13 26 77 101 Other parts of India Kolkata 32 10 19 57 49 Other parts of India South Bihar 18 9 12 46 39 Other parts of India North Bihar 19 10 23 38 61 Other parts of India Lucknow 15 7 19 33 35 Other parts of India Dehli 11 4 20 22 20 Other parts of India Mhow 12 4 29 31 34 Other parts of India Jodhpur 14 6 21 20 17 Other parts of India Kathiawar 11 5 19 22 21 Other parts of India North Gujarat 4 5 18 17 15 Other parts of India South Gujarat 22 10 25 46 42 Other parts of India Pune 19 10 50 41 44 Other parts of India Konkan 23 11 19 35 42 Other parts of India Karwar 54 16 21 66 66 Other parts of India Coorg 60 18 31 80 89 Other parts of India Bangalore 20 9 25 50 36 Other parts of India Travancore 37 15 26 58 84 Other parts of India Palani Hills 46 15 30 83 75 Other parts of India Nilgiri Hills 64 23 35 82 90 Other parts of India Eastern Ghats 23 12 21 31 30 Other parts of India Sikanderabad 7 5 14 20 24 Other parts of India Andaman Nikobar 42 13 20 68 74 Sri Lanka Sri Lanka 48 15 29 82 68 North Myanmar Chindwin District 65 24 26 81 124 North Myanmar Arakan coast 21 15 21 42 60 North Myanmar Shan States 28 16 25 64 95 South Myanmar South Myanmar 189 50 40 272 237

In cell H2, get the unique Area values using UNIQUE(). In column I, use COUNTIF() to get the count of each Area. Pass the data range from A2 to A45 as absolute addresses, and the filter criteria from H2 to H9. In column J, use COUNTIFS() to get the count of each area with more than 20 Swallow-tails. the first two arguments are the same as the previous step, then add a condition for column D to be greater than 20. Area Locality Skipper Swallowtail White-yellow Blue Brush-footed Area Count of Area Count of Area with 20 Swallowtails Indian Subcontinent Indian Subcontinent 307 94 99 458 482 Western Himalaya Western Himalaya 63 31 42 129 152 Western Himalaya Kangra Hills 25 23 37 56 87 Western Himalaya Shimla Hills 41 21 34 88 115 Western Himalaya Dehradun Valley 22 11 19 42 54 Western Himalaya Mussoorie Hills 54 23 32 88 126 Western Himalaya Mussoorie Town 14 10 13 44 65 Western Himalaya Kumaon Hills 52 26 37 109 147 Central Himalaya Central Himalaya 125 43 49 185 221 North East India + North Myanmar North East India + North Myanmar 211 69 57 284 342 North East India + North Myanmar North East India 189 62 52 258 292 North East India + North Myanmar Sikkim 159 55 51 162 263 North East India + North Myanmar Darjeeling 27 29 32 48 126 North East India + North Myanmar Naga Hills 67 38 30 110 178 North East India + North Myanmar Manipur 119 18 1 126 57 North East India + North Myanmar Khasia Hills 98 42 36 128 207 North East India + North Myanmar Khasia + Jaintia Hills 132 49 40 166 209 North East India + North Myanmar Mizoram Hills 59 13 26 77 101 Other parts of India Kolkata 32 10 19 57 49 Other parts of India South Bihar 18 9 12 46 39 Other parts of India North Bihar 19 10 23 38 61 Other parts of India Lucknow 15 7 19 33 35 Other parts of India Dehli 11 4 20 22 20 Other parts of India Mhow 12 4 29 31 34 Other parts of India Jodhpur 14 6 21 20 17 Other parts of India Kathiawar 11 5 19 22 21 Other parts of India North Gujarat 4 5 18 17 15 Other parts of India South Gujarat 22 10 25 46 42 Other parts of India Pune 19 10 50 41 44 Other parts of India Konkan 23 11 19 35 42 Other parts of India Karwar 54 16 21 66 66 Other parts of India Coorg 60 18 31 80 89 Other parts of India Bangalore 20 9 25 50 36 Other parts of India Travancore 37 15 26 58 84 Other parts of India Palani Hills 46 15 30 83 75 Other parts of India Nilgiri Hills 64 23 35 82 90 Other parts of India Eastern Ghats 23 12 21 31 30 Other parts of India Sikanderabad 7 5 14 20 24 Other parts of India Andaman Nikobar 42 13 20 68 74 Sri Lanka Sri Lanka 48 15 29 82 68 North Myanmar Chindwin District 65 24 26 81 124 North Myanmar Arakan coast 21 15 21 42 60 North Myanmar Shan States 28 16 25 64 95 South Myanmar South Myanmar 189 50 40 272 237

Np Ms Office 365/Excel 2016 I Ntermed

1st Edition

ISBN:9781337508841

Author:Carey

Publisher:Carey

Chapter8: Working With Advanced Functions

Section: Chapter Questions

Problem 4.4CP

See similar textbooks

Related questions

Concept explainers

Question

100%

In cell H2, get the unique Area values using UNIQUE().
In column I, use COUNTIF() to get the count of each Area. Pass the data range from A2 to A45 as absolute addresses, and the filter criteria from H2 to H9.
In column J, use COUNTIFS() to get the count of each area with more than 20 Swallow-tails. the first two arguments are the same as the previous step, then add a condition for column D to be greater than 20.

Area	Locality	Skipper	Swallowtail	White-yellow	Blue	Brush-footed	Area	Count of Area	Count of Area with 20 Swallowtails
Indian Subcontinent	Indian Subcontinent	307	94	99	458	482
Western Himalaya	Western Himalaya	63	31	42	129	152
Western Himalaya	Kangra Hills	25	23	37	56	87
Western Himalaya	Shimla Hills	41	21	34	88	115
Western Himalaya	Dehradun Valley	22	11	19	42	54
Western Himalaya	Mussoorie Hills	54	23	32	88	126
Western Himalaya	Mussoorie Town	14	10	13	44	65
Western Himalaya	Kumaon Hills	52	26	37	109	147
Central Himalaya	Central Himalaya	125	43	49	185	221
North East India + North Myanmar	North East India + North Myanmar	211	69	57	284	342
North East India + North Myanmar	North East India	189	62	52	258	292
North East India + North Myanmar	Sikkim	159	55	51	162	263
North East India + North Myanmar	Darjeeling	27	29	32	48	126
North East India + North Myanmar	Naga Hills	67	38	30	110	178
North East India + North Myanmar	Manipur	119	18	1	126	57
North East India + North Myanmar	Khasia Hills	98	42	36	128	207
North East India + North Myanmar	Khasia + Jaintia Hills	132	49	40	166	209
North East India + North Myanmar	Mizoram Hills	59	13	26	77	101
Other parts of India	Kolkata	32	10	19	57	49
Other parts of India	South Bihar	18	9	12	46	39
Other parts of India	North Bihar	19	10	23	38	61
Other parts of India	Lucknow	15	7	19	33	35
Other parts of India	Dehli	11	4	20	22	20
Other parts of India	Mhow	12	4	29	31	34
Other parts of India	Jodhpur	14	6	21	20	17
Other parts of India	Kathiawar	11	5	19	22	21
Other parts of India	North Gujarat	4	5	18	17	15
Other parts of India	South Gujarat	22	10	25	46	42
Other parts of India	Pune	19	10	50	41	44
Other parts of India	Konkan	23	11	19	35	42
Other parts of India	Karwar	54	16	21	66	66
Other parts of India	Coorg	60	18	31	80	89
Other parts of India	Bangalore	20	9	25	50	36
Other parts of India	Travancore	37	15	26	58	84
Other parts of India	Palani Hills	46	15	30	83	75
Other parts of India	Nilgiri Hills	64	23	35	82	90
Other parts of India	Eastern Ghats	23	12	21	31	30
Other parts of India	Sikanderabad	7	5	14	20	24
Other parts of India	Andaman Nikobar	42	13	20	68	74
Sri Lanka	Sri Lanka	48	15	29	82	68
North Myanmar	Chindwin District	65	24	26	81	124
North Myanmar	Arakan coast	21	15	21	42	60
North Myanmar	Shan States	28	16	25	64	95
South Myanmar	South Myanmar	189	50	40	272	237

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 4 steps with 3 images

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.