About this exercise
A 300-row dataset on one sheet, one answer cell, about 7 minutes. Graded in your browser: the value and the formula behind it, with feedback that names the fix.
How to solve it
- G2
=COUNTIFS($D$2:$D$301,$F2,$C$2:$C$301,G$1)
COUNTIFS counts the rows where every range/criterion pair is satisfied at the same time: region equals the row label and month equals the column label. The mixed references do the rest: $F2 always reads the region in column F of the cell's own row, G$1 always reads the month in row 1 of the cell's own column, and the data ranges are fully locked. One formula therefore fills all twelve cells, and the counts add up to the 300 orders because every order has exactly one region and one month.
Pitfall 1: locking the wrong half (F$2 and $G1) makes the first row and column look right and everything else wrong — check the bottom-right cell's formula after filling. Pitfall 2: the Month column must match the header text exactly ("Jul", not "July" or "07"); when the export has only dates, add two date conditions per month (">="&first day, "<="&last day) or a helper column =TEXT(B2,"mmm"). Pitfall 3: COUNTIF (singular) takes one pair only; typing COUNTIF with two pairs returns a #VALUE! or counts the wrong thing. Alternatives: SUMPRODUCT(($D$2:$D$301=$F2)*($C$2:$C$301=G$1)) is the array form and also works in very old versions; a pivot table gives the same grid in four clicks but does not refresh automatically with a pasted export unless you refresh it. In Excel 365, =COUNTIFS($D$2:$D$301,F2:F4,$C$2:$C$301,G1:J1) in G2 spills the whole 3 × 4 grid from one cell.
Other ways that work
- =SUMPRODUCT(($D$2:$D$301=$F2)*($C$2:$C$301=G$1)) — array form, same mixed references
- =COUNTIFS($C$2:$C$301,G$1,$D$2:$D$301,$F2) — the pairs in the other order
- =COUNTIFS($D$2:$D$301,F2:F4,$C$2:$C$301,G1:J1) in G2 — Excel 365 only; one spilling formula for the grid
Common mistakes
Filling =COUNTIFS($D$2:$D$301,F2,$C$2:$C$301,G1) across without the mixed $: in H2 the labels slide to G2 (an answer cell) and H1, so the second column onwards counts the wrong things or returns 0.
How we grade
Any formula that produces the right result is accepted, unless the task names a function: then another function earns half credit. We compare values with a small tolerance and ignore case and spaces in text. A correct number typed by hand instead of a formula earns half credit. Multi-cell tasks earn full credit at 100% of cells, half credit from 50%. Where it matters, we also change an input cell on a copy of your sheet and check that your formula follows.
Answers computed with Excel 365 rules and checked in our grading engine on November 16, 2026. Author: GridBelt team.