Sobre este exercício
Um conjunto de dados de 300 linhas em uma planilha, 3 células de resposta, cerca de 10 minutos. Corrigido no seu navegador: o valor e a fórmula por trás dele, com feedback que mostra o que corrigir.
Como resolver
- G2
=SUMIFS($D$2:$D$301,$C$2:$C$301,$F2,$B$2:$B$301,">="&G$1,$B$2:$B$301,"<"&EDATE(G$1,1)) - M2
=SUM(G2:L2) - G7
=SUM(G2:G6)
A PivotTable that groups dates by Months puts every order into the calendar month it falls in, from the 1st to the last day. The SUMIFS rebuilds that bucket with two conditions on the same date column — ">="&G$1 (on or after the month's 1st) and "<"&EDATE(G$1,1) (before the next month's 1st) — plus the category condition. Mixed references let one formula fill all 30 cells, and the grand totals reconcile to SUM(D2:D301) because every order lands in exactly one cell.
The PivotTable route in Excel: Insert → PivotTable, Category to Rows, Order date to Columns, Revenue to Values (Sum). Excel 2016 and later group a date field automatically; to set the grouping yourself, right-click a date → Group → Months (the labels then read Jan, Feb, …; select Years as well when the data spans more than one year, or January of both years lands in one column). The pivot needs Refresh after new data; these formulas recalculate by themselves, as long as a new export fits in rows 2–301 (for a longer one, extend the ranges or keep the data in an Excel Table). Pitfall 1: the bounds. ">"&G$1 drops every order of the 1st (the deal day), "<="&EDATE(G$1,1) counts the next month's 1st in two months, "<"&EOMONTH(G$1,0) drops the last day (the B2B invoices). Pitfall 2: timestamps. An order at 31 Jan 14:30 is later than EOMONTH(G$1,0), which is midnight, so "<="&EOMONTH(…) misses it, while "<"&EDATE(G$1,1) is safe with times too. Pitfall 3: text headers such as "Jan" cannot be compared with dates; with real dates in row 1 the same formula works for any month and year, and changing G1 re-targets its whole column. In Excel 365, =SUMIFS(D2:D301,C2:C301,F2:F6,B2:B301,">="&G1:L1,B2:B301,"<"&EDATE(+G1:L1,1)) in G2 spills the whole grid from one cell — the + in front of G1:L1 matters, because Excel's EDATE rejects a plain range with #VALUE! but accepts an array. PIVOTBY builds a cross-tab with its own headers (=PIVOTBY(C2:C301,TEXT(B2:B301,"yyyy-mm"),D2:D301,SUM)); this practice grid does not support PIVOTBY.
Outras formas que funcionam
- =SUMIFS($D$2:$D$301,$C$2:$C$301,$F2,$B$2:$B$301,">="&G$1,$B$2:$B$301,"<="&EOMONTH(G$1,0)) — the month end as the upper bound (fine while the dates carry no times)
- =SUMPRODUCT(($C$2:$C$301=$F2)*(TEXT($B$2:$B$301,"yyyy-mm")=TEXT(G$1,"yyyy-mm"))*$D$2:$D$301) — compare year and month as text
- =SUMIFS(D2:D301,C2:C301,F2:F6,B2:B301,">="&G1:L1,B2:B301,"<"&EDATE(+G1:L1,1)) in G2 — Excel 365, one formula spills the whole grid (keep the + before G1:L1)
- =SUMIFS($D$2:$D$301,$C$2:$C$301,$F2) in M2 — a category total straight from the data instead of adding up the row
Erros comuns
Writing ">"&G$1 instead of ">="&G$1: every order placed on the 1st — the monthly deal day — drops out of its month, so G2 already misses part of the January Accessories revenue.
Como corrigimos
Qualquer fórmula que produza o resultado certo é aceita, a menos que a tarefa cite uma função: nesse caso, outra função vale metade da pontuação. Comparamos os valores com uma pequena tolerância e ignoramos maiúsculas e espaços no texto. Um número certo digitado à mão em vez de uma fórmula vale metade da pontuação. Tarefas com várias células valem a pontuação integral com 100% das células certas e metade a partir de 50%. Quando faz diferença, também mudamos uma célula de entrada em uma cópia da sua planilha e verificamos se sua fórmula acompanha.
Respostas calculadas com as regras do Excel 365 e verificadas no nosso corretor em 20 de novembro de 2026. Autor: GridBelt team.