Level 4 · Return rate per category with COUNTIFS

A return rate needs two numbers: how many lines came back, and how many were sold. Jack has both, in two different shapes. The returns are a list, one row per returned line, so you count them. The lines sold come from the full November export as one number per category, already typed into H2:H7.

The list in A2:E60 holds the 59 returned lines from completed November 2025 orders, the same orders the revenue numbers use. It leaves out refunded orders: those were paid back in full, never counted as revenue, and nearly every line on them comes back, so they would only blur the rate.

COUNTIFS with one criterion

COUNTIFS takes pairs of a range and a criterion and counts the rows where all of them hold:

=COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2], …)

There is no range to add up, only ranges to test. In I2:

I2   =COUNTIFS($B$2:$B$60,G2)

Decor (G2) has 17 returned lines. Filled down to I7: Furniture 3, Kitchen 10, Lighting 7, Outdoor 1, Textiles 21. Matching is not case sensitive, so decor would count the same rows.

From a count to a rate

J2   =I2/H2

Decor: 17 ÷ 464 = 3.66%. With the column formatted as a percentage, the cell holds 0.0366 and shows 3.66%; type =I2/H2*100 and the format shows 366.38%. Textiles has the highest rate at 5.30%, Outdoor the lowest at 2.00%.

A second criterion from an input cell

Damaged items go to the carrier claim, so Jack wants them counted on their own. The reason sits in H10, so the claim team can switch it to late or wrong_item without touching a formula:

K2   =COUNTIFS($B$2:$B$60,G2,$C$2:$C$60,$H$10)

Both conditions must hold on the same row: category Decor and reason damaged. Decor has 6. $H$10 is locked because every row reads the same reason; G2 moves.

The total row

H8, I8 and K8 are plain sums: 1,600 lines, 59 returns, 14 damaged. The overall rate is the total returns over the total lines:

J8   =I8/H8

That is 3.69%. Typing =AVERAGE(J2:J7) instead gives 3.40%, because a plain average gives each category the same weight whatever it sold. A rule for every report: a rate in a total row is the ratio of the totals, never the average of the rates above it.

Read it before you send it

• Small denominators. Outdoor’s 2.00% is 1 return out of 50 lines. One more return would make it 4%. Say “1 of 50” next to the rate.
• Returns are still coming. The data runs to December 31, and a customer can send a late November order back in January. November’s rate can only go up; call it a floor.

Common mistakes

• COUNTIF with two criteria. COUNTIF takes one range and one criterion; the second pair needs COUNTIFS.
• Averaging the rates in the total row: 3.40% instead of 3.69%.
• Typing "damaged" into the formula. It works today, but the claim team can’t change the reason without editing six formulas.
• Leaving H10 unlocked. Filled down, it becomes H11, an empty cell, and empty matches nothing, so the damaged counts below Decor fall to 0.