Lambda MAP combined with HSTACK

What you need to do is perform the sum calculation first, then stack and then pull unique values:

=LET(y,YEAR(D3:D96),u,F3:F96,UNIQUE(HSTACK(y,BYROW(y,LAMBDA(r,SUMPRODUCT((y=r)*u))))))

LAMBDA doesn't like SUM(IF or SUMIF(S).

Originally Posted by AliGW

LAMBDA doesn't like SUM(IF or SUMIF(S).

That is not true. LAMBDA will work fine with SUMIF(S), subject to the usual constraints of those functions.

REDUCE and MAP are very different. REDUCE returns one result (though that can be an array) whereas MAP returns a result for every item of the input array. If you try and use HSTACK with MAP, you'd be trying to return increasingly large arrays for each item of the original array, which couldn't possibly work.

Originally Posted by romperstomper

That is not true. LAMBDA will work fine with SUMIF(S), subject to the usual constraints of those functions.

I didn't say it didn't work, rather that it didn't like those functions. I was specific in my choice of words. There are occasions where they won't work, but SUMPRODUCT will.

Yes, there are occasions where they won't work, but that has nothing to do with the use of LAMBDA, which has no effect on the operation of functions it calls. Your statement was misleading at best.

BTW, if you're going to edit people's posts it would be nice if you left a note indicating why?

I didn't edit it - I accidentally clicked the wrong button when aiming for the Reply With Quote button and typed my reply into your post before I realised what I'd done. I then had to remove it - that's all. I can send you a log of the changes, if you'd like. I'll add an edit note - should have done that. Need more coffee ...

I accept that my statement might heve been misleading, but I've seen those functions trip so many people up with LAMBDA that I think it's useful to float the possibility of using SUMPRODUCT.

I don't need a forensic accounting: I was curious precisely because I couldn't see any change! Now it makes sense.

Incidentally, the OP didn't even use SUMIF - it was SUM(IF( which doesn't have the same limitations anyway...

OK - for the benefit of everyone here and for the record, could you please explain these limitations so that the OP knows what they are? I'm not very good at this sort of thing and don't want to cause any further misunderstandings.

I don't need a forensic accounting

Sorry - damned if you do, damned if you don't!

Sure: the limitations of the xxxIF(S) functions (e.g. SUMIF/SUMIFS/COUNTIF/AVERAGEIF) are that everything apart from the criteria for the IF part have to be actual ranges on a sheet, not arrays. So, for example, with SUMIF:

=SUMIF(criteria_range, criterion,sum_range)

The first and third arguments have to be ranges. (the criterion itself may or may not be)

The main limitation of that is that you can't do other calculations on a range and then pass it to the functions. So, let's say you have dates in A2:A10 and values in B2:B10 for example, and you want to sum values for a specific year. You cannot do something like:

=SUMIF(YEAR(A2:A10),2024, B2:B10)

because the YEAR function will return an array of numbers, not an actual worksheet range. You either have to use a different function, or use SUMIFS and specify start and end dates of the relevant year:

=SUMIFS(B2:B10,A2:A10,">="&DATE(2024,1,1), A2:A10,"<="&DATE(2024,12,31))

Similarly, you can't use functions like UNIQUE or FILTER to restrict the ranges passed to these functions because, again, they will return arrays, not ranges.

But, as Ali, has suggested (and I have used), SUMPRODUCT does not have the same limitation. Yes?

@romperstomper: are these limitations documented somewhere, or have you just learned (and dissected) over time?

SUMPRODUCT doesn't have the same limitations, nor does SUM(IF(

I don't know that there is documentation that states you can't use arrays, but the help for those functions only mentions ranges, not arrays, so I guess it's implied. It's why they don't work with closed workbooks.

Thanks Rory. Until recently, I’d never even thought about Ranges and Arrays, let alone a difference between them.