Pular para o conteúdo
GridBelt
  • Teste de Excel
  • Prática
  • Programas
  • Preços
Entrar
Começar o teste gratuito
  • Teste de Excel
  • Prática
  • Programas
  • Preços
EntrarCriar conta
Começar o teste gratuito
GridBelt

Pratique Excel no seu navegador.

Teste de Excel

  • Nível 1: Iniciante
  • Nível 2: Intermediário
  • Nível 3: Avançado
  • Teste de Excel em 7 dias
  • Ranking
  • Teste de Excel para entrevista
  • Avaliação de Excel da Indeed

Prática

  • Todos os exercícios
  • Programas
  • Para iniciantes

Guias

  • Fórmulas do Excel
  • Atalhos do Excel

Empresa

  • Sobre nós
  • Preços
  • Como corrigimos

© GridBelt. Todos os direitos reservados.

  • Política de Privacidade
  • Termos de Uso
  • Política de Reembolso

Excel é uma marca comercial da Microsoft Corporation. Indeed, TestGorilla, SHL e outras marcas pertencem aos seus respectivos proprietários. O GridBelt é um site de prática independente e não tem afiliação com Microsoft, TestGorilla, SHL ou Indeed, nem é endossado ou patrocinado por eles.

  1. Prática
  2. /
  3. Pivot tables

Pivot table practice: revenue by category and month with SUMIFS

  • Intermediário
  • ~10 min
  • Pivot tables

Enunciado em inglês

An office-furniture webshop exported its January–June 2026 order lines: 300 rows with the order date, the product category and the line revenue. The category manager wants the PivotTable she knows — Category in Rows, Order date grouped by Months in Columns, Sum of Revenue in Values, with grand totals — but as formulas that recalculate when the next export is pasted in; this practice grid has no PivotTable command anyway. Two habits of the shop make the month edges matter: every month opens with a deal day on the 1st, and business accounts are invoiced on the last day of the month.

O que você precisa fazer

  1. 1Look at the layout: the categories run down F2:F6, and G1:L1 hold the first day of each month — real dates formatted to show "Jan 2026" and so on, one column per month as in a PivotTable with the dates grouped by Months. A month runs from its 1st up to, but not including, the 1st of the next month.
  2. 2Click G2 and type =SUMIFS($D$2:$D$301,$C$2:$C$301,$F2,$B$2:$B$301,">="&G$1,$B$2:$B$301,"<"&EDATE(G$1,1)). The first pair picks the category; the two date pairs keep the orders from the 1st of the month (">="&G$1) to before the 1st of the next month ("<"&EDATE(G$1,1)) — EDATE(G$1,1) is the same day one month later.
  3. 3Check the $ signs before copying: the data ranges are fully locked, $F2 keeps column F (the category) and G$1 keeps row 1 (the month). F4 (Windows) or ⌘T (Mac) cycles through the variants.
  4. 4Fill G2 right to L2 with the fill handle, then select G2:L6 and press Ctrl+D (Windows) / ⌘D (Mac) to fill the row down to row 6. (Excel also fills a selected block when you type the formula once and press Ctrl+Enter / ⌘+Return; in this practice grid that key runs Check.)
  5. 5Add the grand totals the way a PivotTable shows them: =SUM(G2:L2) in M2, filled down to M6, and =SUM(G2:G6) in G7, filled right to M7.
  6. 6Reconcile: M7 must equal =SUM(D2:D301), because every order falls into exactly one category and one month. If it does not, check the month bounds — ">" instead of ">=" loses the orders of the 1st, and "<=" with EDATE counts the next month's 1st twice.

Células de resposta

Correção

Digite sua fórmula na grade, pressione Enter e depois clique em Verificar. Avaliamos o valor de cada célula de resposta e a fórmula por trás dele.

Tarefas

  1. Não verificadoFill G2:L6 with one SUMIFS formula (mixed references, copied across the grid) that totals Revenue (D2:D301) per category in F2:F6 and per month — from the month-start date in row 1 up to, but not including, the next month's 1st (SUMPRODUCT is accepted too).
  2. Não verificadoAdd the grand totals: M2:M6 for each category, G7:L7 for each month and M7 for the whole grid.

Recomendamos um computador para as tarefas de planilha. A planilha rola dentro desta caixa.

StartInsertFormulasDataView
A1fxPreparando sua pasta de trabalho…
A
B
C
D
E
F
G
H
I
J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Sheet1

Digite sua fórmula na planilha e depois verifique.

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.

Nestes programas

  • Excel test in 7 days, Plano do nível 3Dia 4
  • Excel for Data AnalystsDia 3
  • Excel for Accounting and BookkeepingDia 5

Exercícios relacionados

  • Sales by region and product with SUMIFSIntermediário, ~7 min
  • Orders per region and month with COUNTIFSIntermediário, ~7 min
  • A pivot-style sales report by region with GROUPBYIntermediário, ~6 min

Referência da função

Documentação oficial

Próximo exercício

A pivot-style sales report by region with GROUPBY