SUMIFS with multiple criteria and a date range

How it works, step by step

1. 01$C$2:$C$5000 is the sum range, the Revenue values that get added.
2. 02$B$2:$B$5000,G1 keeps only rows where Region equals the region in G1, for example West.
3. 03$A$2:$A$5000,">="&G2 keeps rows dated on or after the start date in G2. The & joins the operator to the date.
4. 04$A$2:$A$5000,"<="&G3 keeps rows dated on or before the end date in G3.
5. 05SUMIFS adds Revenue only for rows that pass all three tests, so the conditions combine as AND.

The same logic in four tools

=SUMIFS($C$2:$C$5000,$B$2:$B$5000,G1,$A$2:$A$5000,">="&G2,$A$2:$A$5000,"<="&G3)

=SUMIFS($C$2:$C$5000,$B$2:$B$5000,G1,$A$2:$A$5000,">="&G2,$A$2:$A$5000,"<="&G3)

SELECT SUM(revenue) AS revenue
FROM sales
WHERE region = 'West'
  AND order_date >= DATE '2026-01-01'
  AND order_date <= DATE '2026-03-31';

import pandas as pd

mask = (
    (sales["region"] == "West")
    & (sales["order_date"] >= "2026-01-01")
    & (sales["order_date"] <= "2026-03-31")
)
revenue = sales.loc[mask, "revenue"].sum()

Mistakes that break it

• !Dates stored as text (left-aligned in the cell) fail the >= and <= tests and drop out of the total. Convert them with DATEVALUE or Data > Text to Columns.
• !If Order date includes a time, "<="&G3 drops everything after midnight on the end date. Use "<"&G3+1 to include the whole last day. The same applies to SQL timestamp columns: use order_date < DATE '2026-04-01'.
• !Every range must be the same size. $C$2:$C$5000 paired with $B$2:$B$4999 returns #VALUE!.
• !SUMIFS conditions are AND only. For West or East in Excel, use =SUM(SUMIFS($C$2:$C$5000,$B$2:$B$5000,{"West","East"})) or add two SUMIFS together.

Common questions

What is the SQL equivalent of SUMIFS?

SUM with a WHERE clause. Each SUMIFS criteria pair becomes one condition joined with AND, and adding GROUP BY region gives a total per region instead of one number.

How do I use SUMIFS with a date range?

Use the date column twice with two criteria: ">="&G2 for the start and "<="&G3 for the end, where G2 and G3 hold real dates.

What is the difference between SUMIF and SUMIFS?

SUMIF takes one condition and puts the sum range last. SUMIFS takes one or more conditions and puts the sum range first, so many analysts use SUMIFS for everything.