Sum unique values over a specific year

[bobh63]

Column A is a list of dates in the format mm/dd/yyyy. Over many months/years.
Column M is a list of numbers that can repeat.
R12 is a year in the format yyyy.

Why is this not working:

=SUMIFS(UNIQUE(M:M),A:A,">="&DATE(R12,1,1),A:A,"<="&DATE(R12,12,31))

Excel does not like it. Message is no help.

Works without the embedded UNIQUE function. But I only want to sum unique numbers. If this does not work, anyone have an alternative.

Thank you,
Bob

 

[StephenCrump]

Welcome to the Forum!

The problem with the formula is the mismatch in size between UNIQUE(M:M) and A:A. SUMIFs can't accommodate this.

Try: =SUM(UNIQUE((IF(YEAR(A:A)=R12,M:M))))

 

[Peter_SSs]

Try: =SUM(UNIQUE((IF(YEAR(A:A)=YEAR(R12),M:M))))

Hi Stephen
Looks like you have an extra YEAR() in there. Should be this?

=SUM(UNIQUE((IF(YEAR(A:A)=R12,M:M))))

@bobh63
Welcome to the MrExcel board!

Your profile says excel 2019 but you apparently have the UNIQUE function which is not available in 2019. Perhaps your profile needs adjusting?

I would strongly recommend against using whole column references in formulas like that. Using the formula above, I can see the (small but quite noticeable) delay while Excel calculates all 1 million+ rows when I assume nothing like that many is actually used?

So even if using the above structure, I would limit the ranges to some smaller number that would still easily accommodate your data.
Here is another alternative to consider (adjusting ranges if required) assuming you do have access to UNIQUE (& therefore also FILTER).

=SUM(UNIQUE(FILTER(M1:M10000,YEAR(A1:A10000)=R12,0)))

 

[StephenCrump]

Looks like you have an extra YEAR() in there.

Ha ha, yes, I did, thanks Peter. I thought I'd corrected before anyone would notice, so I didn't mark it as an edit.

 

[Peter_SSs]

r. I thought I'd corrected before anyone would notice,

Ah, I must have opened the page and left it sitting there for a while before I posted and hadn't noticed your edit.

 

[bobh63]

Thank you for the responses.
Both offered solutions return #VALUE!
- =SUM(UNIQUE((IF(YEAR(A:A)=R12,M:M))))
- =SUM(UNIQUE(FILTER(M1:M10000,YEAR(A1:A10000)=R12,0)))
I'm am indeed using 2019 Pro as documented in File > Account - Say's "Product Activated: Microsoft Office Professional Plus 2019".
I am using both UNIQUE and FILTER in this very spreadsheet in another area and they both work just fine.
My data set is just shy of 6k records/rows.
The formula that works, using UNIQUE and FILTER only acts on column M, a column of numeric values, formatted accounting.
This one that does not work, using UNIQUE and FILTER acts on both column M and A, a column of date values, formatted date as 5/22/2022.
Any idea for the #VALUE!

 

[StephenCrump]

If M is working, but A not, it suggests there's a problem with the dates in column A.

Try a helper column: = YEAR(A1), copied down, to identify where the issue might be.