Sobre este exercício
Um conjunto de dados de 80 linhas em uma planilha, 3 células de resposta, cerca de 8 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
=AND(D2<>"",D2<$J$2,F2="") - J3
=COUNTIF(G2:G81,TRUE) - J4
=SUMIF(G2:G81,TRUE,E2:E81)
A conditional-formatting rule of the "use a formula" kind is just a formula that returns TRUE or FALSE for each row, so writing it in a helper column first shows you exactly which rows the rule will colour. AND needs all three tests to hold: D2<>"" (there is a due date), D2<$J$2 (it is in the past relative to the report date) and F2="" (no payment yet). The $ signs lock J2 so every row compares against the same report date when you fill down, while D2 and F2 stay relative and move row by row. COUNTIF and SUMIF then count and total the TRUE rows.
Pitfall 1: in the rule dialog the formula is written for the top-left cell of the selection (A2) and Excel shifts it for every other cell, so the column references need a $ ($D2, $F2) — otherwise the cells in column B would test E and G instead. Pitfall 2: an empty cell compares as 0, so D2<$J$2 is TRUE for an invoice without a due date — 3 invoices here; D2<>"" (or ISNUMBER(D2)) keeps them out. COUNTIFS(D2:D81,"<"&J2,F2:F81,"") gets this right on its own, because COUNTIFS skips blank cells for a "<" criterion. Pitfall 3: < versus <= — an invoice due on the report date (row 77) is not overdue yet; and a paid invoice is never overdue, even a late one, so a rule on the due date alone would also colour the 31 paid invoices whose due date has passed (16 of them were paid late). Alternatives: =IF(AND(...),TRUE,FALSE) is the same with an explicit IF; =(D2<>"")*(D2<$J$2)*(F2="") returns 1 or 0 — enough inside a formatting rule, where any non-zero number counts as TRUE, but this column is checked for TRUE/FALSE; =COUNTIFS(D2:D81,"<"&J2,F2:F81,"") and =SUMIFS(E2:E81,D2:D81,"<"&J2,F2:F81,"") count and total without the helper column. Reporting: TODAY() instead of a typed report date keeps the list live, but the file then changes every day and cannot be reproduced for an audit — many controllers keep the report date in a cell exactly as here. Windows shortcuts: Alt, H, L, N opens New Rule and Alt, H, L, R manages the rules; on Mac use Home → Conditional Formatting → Manage Rules.
Outras formas que funcionam
- =AND(ISNUMBER(D2),D2<$J$2,ISBLANK(F2)) — the same tests with ISNUMBER and ISBLANK
- =IF(AND(D2<>"",D2<$J$2,F2=""),TRUE,FALSE) — explicit IF around the same test
- =COUNTIFS(D2:D81,"<"&J2,F2:F81,"") and =SUMIFS(E2:E81,D2:D81,"<"&J2,F2:F81,"") — count and amount without the helper column
- =SUMPRODUCT(--G2:G81) — counts the TRUE values by turning them into 1s
Erros comuns
=AND(D2<$J$2,F2="") without the due-date test: an empty due date counts as 0, so the 3 invoices with no terms (rows 21, 45, 64) show TRUE and the count in J3 comes out 27 instead of 24.
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 16 de novembro de 2026. Autor: GridBelt team.