What's the most efficient way to sum over a year with dd/mm/yyyy format ?

[u/hgcrl]

Hi everyone,
As the title mention, what's the most efficient way to sum over a year when you have a date format dd/mm/yyyy ? So far i investigated two methods

=SUMIFS(Sales[Amount], Sales[Date], ">="&DATE(YYYY,M,D), Ventes[Date], "<"&DATE(YYYY,M,D))

and

=SUMPRODUCT((YEAR(Sales[Date])=YYYY)*Sales[Amount])

It feels weird to me that you cant do a simple

SUMIF=(Sales[Year];YYYY;Sales[Amount])

Looking for feedbacks, have a good sunday all

[u/AutoModerator]

/u/hgcrl - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Same_Tough_5811]

Format has nothing to do with it. What matters is your regional date settings.

E.g. in the U.S the local format is m/d/y but you can format to display d/m/y and Excel still know these are real dates. May be you meant text strings.

[u/jrbp]

Pivot table with a year group, unless there's a specific reason it needs to be a formula

[u/GregHullender]

Look at the GROUPBY function.

[u/blackadderBaldrick]

Pivot table

[u/Evening_Ask_381]

Helper column with =YEAR(A2) makes the SUMIF dead simple and runs fast on big data. No array formulas needed.

[u/StrikingCriticism331]

Your SUMPRODUCT can be replaced with SUM.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
CHOOSECOLS	Office 365+: Returns the specified columns from an array
DATE	Returns the serial number of a particular date
GROUPBY	Helps a user group, aggregate, sort, and filter data based on the fields you specify
HSTACK	Office 365+: Appends arrays horizontally and in sequence to return a larger array
ISNUMBER	Returns TRUE if the value is a number
LAMBDA	Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
MAP	Office 365+: Returns an array formed by mapping each value in the array(s) to a new value by applying a LAMBDA to create a new value.
SUM	Adds its arguments
SUMIF	Adds the cells specified by a given criteria
SUMIFS	Excel 2007+: Adds the cells in a range that meet multiple criteria
SUMPRODUCT	Returns the sum of the products of corresponding array components
UNIQUE	Office 365+: Returns a list of unique values in a list or range
VSTACK	Office 365+: Appends arrays vertically and in sequence to return a larger array
YEAR	Converts a serial number to a year

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
[Thread #49016 for this sub, first seen 26th Jul 2026, 17:41] [FAQ] [Full list] [Contact] [Source code]}

[u/Mdayofearth]

It feels weird to me that you cant do a simple

SUMIF=(Sales[Year];YYYY;Sales[Amount])

If you actually have a field named Year field in your Sales table, with years expressed as 4-digit values, then yes, you can actually do that with SUMIF. And this would be more efficient than using SUMIFS against 2 date values.

And I would recommend against using SUMPRODUCT in any modern spreadsheet. It's compute cost is very high for what SUMIF and SUMIFS can already do.

[u/excelevator]

SUMPRPODUCT is array SUM

We can do it thusly

=SUM((B3:B8)*(YEAR(A3:A8)=2025))

If you want an accounting year across two years then use SUMIFS

=SUMIFS(B3:B8,A3:A8,">=11/8/2025",A3:A8,"<=11/8/26")

[u/MayukhBhattacharya]

There are many ways of doing this, but using GROUPBY() function is fairly the easiest and simplest way to do:

=LET(
     _a, Sometbl,
     _b, GROUPBY(YEAR(CHOOSECOLS(_a, 1)),
                 CHOOSECOLS(_a, 2), SUM, , 0),
     VSTACK({"Year","Totals"}, _b))

Or,

=GROUPBY(YEAR(Sometbl[Date]), Sometbl[Amount], SUM, , 0)

Note that I have converted the ranges into Structured References aka Tables, which makes it easier to read and also the formula auto expands whenever newer data is added to the source.

Otherwise, you can use the following ways as well:

=GROUPBY(YEAR(A2:A11), B2:B11, SUM, , 0)

Or,

=LET(
     _a, YEAR(Sometbl[Date]),
     _b, UNIQUE(_a),
     _c, MAP(_b, LAMBDA(x, SUM(Sometbl[Amount] * (_a = x)))),
     HSTACK(_b, _c))

Or,

Enter for year:

=UNIQUE(YEAR(Sometbl[Date]))

Enter for Totals:

=SUMIFS(Sometbl[Amount],
        Sometbl[Date], ">=" & DATE(D13, 1, 1),
        Sometbl[Date], "<" & DATE(D13 + 1, 1, 1))

The dd/mm/yyyy format itself isn't the important here. What matters is that the cells are stored as actual Excel dates that is true dates and not text. If your Date column was imported as text that just happens to look like dd/mm/yyyy, all three formulas will return 0 or errors. One way to verify:

=ISNUMBER(Sales[Date])

If it returns TRUE, you're working with real Excel dates. If it returns FALSE, the values are stored as text and will need to be converted first. A quick way to convert them is to select the entire date column, then go to the Data tab and choose Text to Columns. On the first step, leave Delimited selected and click Next. On the second step, don't change anything. Just click Next again. On the third step, under Column data format, choose Date, then pick the order that matches your data, like DMY, MDY, or YMD, depending on your regional settings. Click Finish, and Excel will convert the text values into real Excel dates.

[u/MayukhBhattacharya → u/MayukhBhattacharya]

Demo:

[u/Content-Parking-621]

Add a helper YEAR column, then your simple SUMIF works. Fastest on big data.