FORMULA & SQL / TOTALS

Total for each year.
Tested, not guessed.

The Excel and Google Sheets formula and the SQL query, each run on the rows below. Open this with your own data ↗

The example rows

Row	A: Region	B: Rep	C: Amount	D: Order Date	E: Status	F: Customer
2	North	Ana	1,200	2026-03-01	Paid	Jo Smith
3	South	Ben	450	2026-03-15	Open	Raj Patel
4	North	Cy	300	2026-04-02	Paid	Lee Wong
5	East	Ana	980.50	2026-04-20	Paid	Mia Ross
6	North	Ben	75	2026-05-05	Open	Jo Smith
7	West	Dee	1,640	2026-05-18	Paid	Sam Okoro

The formula

Excel

=SUMPRODUCT((YEAR($D$2:$D$7)=YEAR(D2))*$C$2:$C$7)

The sum of the year of the Order Date column ($D$2:$D$7) equals the year of Order Date (D2) times the Amount column ($C$2:$C$7).

Gives each row the total for its year.

Run on the example rows:

Row	Result
2	4645.5
3	4645.5
4	4645.5
5	4645.5
6	4645.5
7	4645.5

The SQL

PostgreSQL

SELECT EXTRACT(YEAR FROM order_date) AS sale_year, SUM(amount) AS total
FROM sales
GROUP BY EXTRACT(YEAR FROM order_date)
ORDER BY sale_year

Other databases

MySQL

SELECT YEAR(order_date) AS sale_year, SUM(amount) AS total
FROM sales
GROUP BY YEAR(order_date)
ORDER BY sale_year

SQL Server

SELECT YEAR(order_date) AS sale_year, SUM(amount) AS total
FROM sales
GROUP BY YEAR(order_date)
ORDER BY sale_year

SQLite

SELECT CAST(strftime('%Y', order_date) AS INTEGER) AS sale_year, SUM(amount) AS total
FROM sales
GROUP BY CAST(strftime('%Y', order_date) AS INTEGER)
ORDER BY sale_year

BigQuery

SELECT EXTRACT(YEAR FROM order_date) AS sale_year, SUM(amount) AS total
FROM sales
GROUP BY EXTRACT(YEAR FROM order_date)
ORDER BY sale_year

Snowflake

SELECT YEAR(order_date) AS sale_year, SUM(amount) AS total
FROM sales
GROUP BY YEAR(order_date)
ORDER BY sale_year

Your own data

Paste your rows into the free tool and it writes this for your columns, then runs it on your data before you copy it. Open the tool ↗

Total for each year. Tested, not guessed.

The example rows

The formula

The SQL

Your own data

Related

Total for each year.
Tested, not guessed.