Ravindra BagaleCourses & study guides मराठी Track your progress

Analyse Blinkit Orders in Excel: Total Orders, Cities, Products and Payment Mode with COUNTA, UNIQUE and COUNTIF

By Last updated:

In the previous chapter you learned to understand an orders dataset before touching a formula: what one row means, what every column says and which business questions the data can answer. Now we start answering them. The first questions any manager asks are simple: How many orders did we get? In how many cities? How many products do we sell? How do customers pay? In this chapter you answer all of them with three functions, COUNTA, UNIQUE and COUNTIF, and turn the counts into a % share.

What you'll learn in this chapter

  • The orders table used in the next three chapters
  • Total orders: why COUNT gives 0 and COUNTA gives 24
  • Distinct cities and products with UNIQUE, COUNTA and ROWS
  • Orders per payment mode with COUNTIF and a locked range
  • % share with an absolute reference
  • Status and delivery status counts, and TRANSPOSE

Practice file: blinkit-orders-counta-unique-countif-practice.xlsx: the Orders sheet with every answer from this chapter in column O onwards, two common mistakes in red, a Read me sheet and a Your turn sheet.

Made-up data: all 24 orders, areas, products, prices and profits are made up for practice. No real customer, shop or order is used, and product names are generic.

1. The orders table

This is a slimmer version of the orders file from the dataset chapter: 24 orders from a quick-commerce app (think Blinkit or Amazon Now) in Pune, Nashik, Solapur, Kolhapur, Nagpur and Sambhaji Nagar, from 1 to 8 September 2026. Headers are in row 1 and the orders in rows 2 to 25, columns A to M:

Col Column What it holds
A Order_ID text such as ORD1001
B Order_Date date of the order
C City one of six cities
D Area area inside the city
E Product_Name Milk, Bread, Eggs, Fruits, Snacks, Paneer
F Quantity units ordered
G Cost_Price price per unit the store paid
H Selling_Price price per unit the customer paid
I Payment_Mode Online or COD (cash on delivery)
J Status Delivered, Returned or Cancelled
K Delivery_Time_mins minutes to deliver (empty if cancelled)
L Delivery_Status On Time (15 minutes or less) or Late
M Profit Quantity × (Selling_Price − Cost_Price) − 20

The 20 in Profit is a fixed delivery cost per order. Some orders make a loss: 2 packets of snacks earn 2 × 5 = 10, minus 20 for delivery, so −10.

The first rows:

Order_ID Order_Date City Area Product_Name Quantity Cost_Price Selling_Price Payment_Mode Status Delivery_Time_mins Delivery_Status Profit
ORD1001 01-Sep-2026 Pune Kothrud Milk 6 26 30 Online Delivered 11 On Time 4
ORD1002 01-Sep-2026 Pune Baner Paneer 3 70 90 Online Delivered 13 On Time 40
ORD1003 01-Sep-2026 Nashik Gangapur Road Fruits 4 48 60 COD Delivered 19 Late 28

The same 24 orders are used in this chapter, in the SORT, FILTER and TAKE chapter and in the SUMIFS and MAXIFS chapter, so every answer can be compared across chapters.

One row = one order. 13 columns, A to M Order A Order_ID (text: ORD1001) B Order_Date C City (6 cities) D Area Product and price E Product_Name F Quantity G Cost_Price (per unit) H Selling_Price (per unit) Payment and delivery I Payment_Mode (Online / COD) J Status (Delivered/Returned/Cancelled) K Delivery_Time_mins L Delivery_Status (On Time / Late) Result M Profit = F × (H − G) − 20 20 = delivery cost per order Example row 2: A B C D E F G H I J K L M 2 ORD1001 01-Sep Pune Kothrud Milk 6 26 30 Online Delivered 11 On Time 4

Figure 1. The 13 columns A to M of the orders table, grouped into order, product and price, payment and delivery, and the Profit column.

Classroom line

On the Blinkit data we will need to use a few more new functions.

2. How many orders? COUNT vs COUNTA

Each row is one order and every order has an ID, so count the IDs:

=COUNT(A2:A25)

→ 0. The formula is right, the range is right, and the answer is wrong. COUNT counts only cells that hold numbers, and ORD1001 is text. Use COUNTA, which counts every non-empty cell:

=COUNTA(A2:A25)

→ 24 orders. This is a favourite interview question: "Why does COUNT give 0 on Order_ID?" Because the IDs are text.

Classroom line

The COUNT function won't work, because COUNT works on numbers.

COUNT counts numbers. Order IDs are text. A C 1 Order_ID City 2 ORD1001 Pune 3 ORD1002 Pune 4 ORD1003 Nashik 5 ORD1004 Solapur 6 ORD1005 Kolhapur 7 ORD1006 Nagpur 8 ORD1007 Sambhaji Nagar 9 ORD1008 Pune 10 ORD1009 Nashik … down to row 25 (ORD1024) =COUNT(A2:A25) 0 COUNT looks for numbers only. "ORD1001" is text, so nothing is counted. =COUNTA(A2:A25) 24 COUNTA counts every cell that is not empty: text, numbers, dates. 24 orders. Interview question: "Why does COUNT give 0 on Order_ID?"

Figure 2. The Order_ID column holds text. COUNT(A2:A25) finds no numbers and returns 0; COUNTA(A2:A25) counts all 24 filled cells.

3. How many cities and products? UNIQUE

The City column has 24 entries but only a few different cities. UNIQUE returns each value once:

=UNIQUE(C2:C25)

→ Pune, Nashik, Solapur, Kolhapur, Nagpur, Sambhaji Nagar, in the order they first appear. The formula is in one cell and the list spills down into the cells below it.

Classroom line

The UNIQUE function always returns unique values.

To get the number of cities, count that list:

=COUNTA(UNIQUE(C2:C25))

→ 6 cities. =ROWS(UNIQUE(C2:C25)) gives the same 6: ROWS counts how many rows the list has.

The same for products:

=COUNTA(UNIQUE(E2:E25))

→ 6 products: Milk, Paneer, Fruits, Bread, Eggs and Snacks.

Which Excel has UNIQUE? Excel 2021, Excel 2024, Microsoft 365 and Excel for the web. In older versions, copy the column and use Data › Remove Duplicates to get the list.

UNIQUE lists each city once; COUNTA counts the list C 1 City 2 Pune 3 Pune 4 Nashik 5 Solapur 6 Kolhapur 7 Nagpur 8 Sambhaji Nagar 9 Pune 10 Nashik 11 Kolhapur 12 Nagpur 13 Solapur … down to row 25 S 1 UNIQUE cities 2 Pune 3 Nashik 4 Solapur 5 Kolhapur 6 Nagpur 7 Sambhaji Nagar formula only in S2, the rest spills down =UNIQUE(C2:C25) → 6 cities, in order of first appearance =COUNTA(UNIQUE(C2:C25)) 6 or =ROWS(UNIQUE(C2:C25)) → 6 =COUNTA(UNIQUE(E2:E25)) 6 products: Milk, Paneer, Fruits, Bread, Eggs, Snacks

Figure 3. UNIQUE(C2:C25) spills the six different cities; COUNTA around it counts them: 6.

4. Online or COD? COUNTIF with a locked range

Type the two payment modes as labels, COD in O10 and Online in O11, and count each one with COUNTIF:

=COUNTIF($I$2:$I$25,O10)

→ COD 9 and, after dragging down, Online 15. 9 + 15 = 24, every order.

The $ signs matter. Without them, =COUNTIF(I2:I25,O10) dragged down becomes =COUNTIF(I3:I26,O11): the range slides one row down with the formula. It skips order ORD1001, which is Online, so Online shows 14 and the two counts add up to 23, not 24. Lock the range with $ (press F4) before you drag. The label O10 stays relative so it moves to O11.

Lock the range with $ before you drag COUNTIF Without $ O P 9 Payment Orders 10 COD 9 11 Online 14 12 Total 23 P10: =COUNTIF(I2:I25,O10) P11: =COUNTIF(I3:I26,O11) The range moved down one row and skips ORD1001 (Online). 9 + 14 = 23. With $ O P 9 Payment Orders 10 COD 9 11 Online 15 12 Total 24 P10: =COUNTIF($I$2:$I$25,O10) P11: =COUNTIF($I$2:$I$25,O11) The range stays; only the label moves to O11. 9 + 15 = 24.

Figure 4. Without $ the range slides to I3:I26 and misses the first order: Online 14, total 23. With $I$2:$I$25 both counts are right: 9 + 15 = 24.

Ravindra Bagale's Tip

After any count table, add a check row: =SUM(P10:P11) must equal the total orders (24). If it doesn't, a range has slid or a label has a typo. It takes five seconds and catches the mistake before anyone sees your report.

5. % share with an absolute reference

A count alone doesn't say much; the share does. Total orders sit in P3 (=COUNTA(A2:A25)), so in Q10:

=P10/$P$3*100

→ COD 37.5% and Online 62.5%. The $ on P3 keeps every row dividing by the same total. Without it, the second row becomes =P11/P4*100, where P4 holds the number of cities (6), and Online shows 250%, which is impossible. A share above 100% is a sure sign the total moved.

6. Status: let UNIQUE write the labels

Instead of typing labels, let UNIQUE write them. In O15:

=UNIQUE(J2:J25)

→ Delivered, Cancelled, Returned. Next to it, in P15, =COUNTIF($J$2:$J$25,O15) dragged down, and the share in Q15, =P15/$P$3*100:

Status Orders Share %
Delivered 18 75.00
Cancelled 3 12.50
Returned 3 12.50

So 6 out of 24 orders, one in four, never turned into a sale. That is the kind of finding a manager wants to hear.

Want the labels across a row instead of down a column? Wrap UNIQUE in TRANSPOSE:

=TRANSPOSE(UNIQUE(J2:J25))

→ Delivered | Cancelled | Returned in three cells side by side.

% share = count / $total$ × 100 O P Q 14 Status Orders Share % 15 Delivered 18 75.00 16 Cancelled 3 12.50 17 Returned 3 12.50 P3 holds the total: =COUNTA(A2:A25) → 24 O15 =UNIQUE(J2:J25) P15 =COUNTIF($J$2:$J$25,O15) Q15 =P15/$P$3*100 Why $P$3? Dragged down, a plain P3 becomes P4, P5 … and divides by the wrong cell. In the payment table, Online without $ gives =P11/P4*100 = 250%, because P4 holds the number of cities. A share over 100% means the total moved.

Figure 5. Status labels from UNIQUE, counts from COUNTIF with a locked range, and share = count / $P$3 × 100: Delivered 75%, Cancelled 12.5%, Returned 12.5%.

7. Exercise: On Time vs Late

Count the On Time and Late deliveries (column L) and their share.

  1. =COUNTIF($L$2:$L$25,"On Time") → 15; Late → 6.
  2. 15 + 6 = 21, not 24: the 3 cancelled orders were never delivered, so their Delivery_Status is empty.
  3. So divide by the number of orders that have a delivery status: =COUNTA(L2:L25) → 21 (COUNTA skips empty cells).
  4. On Time share = 15 / 21 × 100 = 71.43%; Late = 28.57%.

=UNIQUE(L2:L25) here returns On Time, Late and 0: the 0 stands for the empty cells. That is UNIQUE telling you the column has blanks.

Ravindra Bagale's Tip

Before you divide, ask "out of what?". Out of all orders, out of delivered orders, or out of orders that have a delivery time? Write the answer next to the % column, for example "share of 21 delivered orders". Two people using two different totals is the most common reason two reports disagree.

8. The whole chapter on one page

Question Formula Answer
Total orders =COUNTA(A2:A25) 24
Same with COUNT =COUNT(A2:A25) 0 (IDs are text)
Cities =COUNTA(UNIQUE(C2:C25)) 6
Products =COUNTA(UNIQUE(E2:E25)) 6
COD / Online =COUNTIF($I$2:$I$25,O10) 9 / 15
Share % =P10/$P$3*100 37.5 / 62.5
Delivered / Cancelled / Returned =COUNTIF($J$2:$J$25,O15) 18 / 3 / 3
On Time / Late =COUNTIF($L$2:$L$25,O21) 15 / 6 (of 21)
  1. COUNT counts numbers only; use COUNTA for text IDs.
  2. UNIQUE lists each value once; COUNTA or ROWS around it counts them.
  3. Lock the COUNTIF range with $ before dragging; leave the label relative.
  4. Share = count / $total$ × 100, and the shares must add up to 100.
  5. UNIQUE: Excel 2021, Excel 2024, Microsoft 365 and Excel for the web.

9. Try at home

Try at home

Task 1

  1. How many different areas are there? (=COUNTA(UNIQUE(D2:D25)) → 13.)

Task 2

  1. Count the orders per city with UNIQUE and COUNTIF. Which city has the most orders? (Pune, 6.)

Task 3

  1. Find the share of orders per product. Do the shares add up to 100?

Task 4

  1. Show the six products across one row with TRANSPOSE.

Recap

In short

  1. COUNT gives 0 on text IDs; COUNTA gives 24.
  2. COUNTA(UNIQUE(range)) counts distinct values.
  3. A COUNTIF range needs $ before you drag it.
  4. % share divides by a locked total.
  5. Check that counts add up to the total.

Quick revision cards

Quick revision

Revise this chapter in two minutes. Save the cards on your phone or download them as PNG files. All names and numbers are made up for practice.

Revision card: Counting orders: COUNTA, UNIQUE, locked ranges. Example data table with Count the orders: 3; How many cities: 2; Share of Online orders: 66.7%.
Card 1 of 2: Counting orders: COUNTA, UNIQUE, locked ranges · Download PNG
Revision card: Check your order counts add up. Example data table with Total orders: 4; Online and COD orders: 3 Online, 1 COD; Do they match?: TRUE.
Card 2 of 2: Check your order counts add up · Download PNG

More cards for every chapter: Ravi Sir's Cards.

Loading practice tasks...

  1. Distinct groups (Easy): How many different values in B2:B11?
  2. List groups (Easy): Unique list of B2:B11.
  3. Filter names (Easy): Names in A2:A11 where B is "Snacks".
  4. Sum of filtered (Medium): Sum of D2:D11 where B is "Dairy", using FILTER.
  5. Count filtered (Medium): Rows where D is above the average of D2:D11.
  6. Sorted values (Medium): D2:D11 sorted largest first.
  7. Top value via SORT (Medium): The largest E2:E11 value using SORT and INDEX.
  8. Second largest (Hard): Second largest D2:D11 using SORT and INDEX.
  9. Numbers 1 to 5 (Hard): A spill of numbers 1 to 5.
  10. Two-condition filter (Hard): Sum of D2:D11 where B is "Snacks" or "Dairy".
  11. Names joined (Hard): Names where B is "Snacks", joined with ", ".

See all practice tasks in the Task Hub →