[ELI5] How does AND condition with SUM via matrix multiplication work?

[zf]

I came across a few SUM formulas like =SUM((A1:A40>=C1)*(A1:A40<C2)*B1:B40) from the only documentation I could find regarding using matrix multiplication to allow the use of AND condition in the SUM formula. How are the comparisons used to generate the matrices involved and how is the matrix multiplication done in order to generate the output? An example that illustrates the quote from the documentation is much appreciated:

The formula is based on the fact that the result of a comparison is 1 if the criterion is met and 0 if it is not met. The individual comparison results will be treated as an array and used in matrix multiplication, and at the end the individual values will be totaled to give the result matrix.

P.S. How do you know when it is possible to use this trick for other formulas?

-------- updated ------

dates-and-condition.ods

[Lupp]

I cannot explain the facts and the relevant conclusions in this case as if you are five. The question is of a serious kind, and lack of related information / analysis occurs often.
You surely know that we tend to think we shouldn’t give driver licenses to five-year-olds. This despite the fact that the basic functionality and the user interface are much simpler for a car than for spreadsheet software.
As also explained below the term “matrix multiplication” is seriously misleading.
You may consider to replace “via matrix multiplication” with “under array-evaluation” by editing your question’s subject.
The tags partly also don’t meet the real subject.

[anon73440385]

Hello,

may be the following sample file, containing a breakdown of the formula, helps to understand how that works:

MatrixMultiplication.ods

Please allow me one comment: Due to the fact that the formula cited in your question doesn’t contain the curly brackets { and }, I need to assume that you missed the important thing here. The formula needs to read:
{=SUM((A1:A40>=C1)*(A1:A40<C2)*B1:B40)}, which is an array formula. Please take care of the difference of entering a formula by finalizing the input with ENTER key or by CTRL+SHIFT+ENTERkey combination, which makes the curly brackets to appear and makes the formula being an array formula.

Hope that helps to understand how that works.

[zf]

Thanks, that makes it much more clear now. I’m not sure if this is related to the original question, but I don’t understand how the following formula works with regards to the third condition in the SMALL function:

=SUMPRODUCT(SMALL(((A$2:A$98="P")*(B$2:B$98>=EDATE(TODAY(),-24))*(B$2:B$98-TODAY())),1))+TODAY()

vs. my attempt, which doesn’t (differs from the correct result above):

=SUMPRODUCT(SMALL(((A$2:A$98="P")*(B$2:B$98>=EDATE(TODAY(),-24))*(B$2:B$98)),1))

I attached an example file in the original post (can’t attach to this comment)–basically I’m trying to find the oldest date within 24 months given a list of dates. Green cell the result of the first formula and red cell is the result of the second. Why is it necessary to subtract TODAY() from the dates in the SMALL and then add it back later, as opposed to letting SMALL act on the date directly?

I understand that the SUMPRODUCT function makes SMALL act as an array formula as an alternative to { }.

[Lupp]

Quoting @zf: “…basically I’m trying to find the oldest date within 24 months given a list of dates…”
Assuming your list of dates is in $A$1:$A$1000, you get this by
=MINIFS($A$1:$A$1000;$A$1:$A$1000;">="&DATE(YEAR(TODAY());MONTH(TODAY())-24;DAY(TODAY())))
You may, of course, also rely on the famous equation “24 months = 2 years”.
Don’t forget to format every cell needing to show a date to the Numbers format code YYYY-MM-DD.

[Lupp]

Concerning the terms:
What the OQer tried is not a matrix multiplication. That’s a completely different thing done by MMULT().

What he (f/m) obviously is trying to achieve is an iterating array-evaluation.
SUM() and AND() both are functions converting arrays placed on their parameter positions to sequences (lists). They are not eligible for array-evaluation therefore, and this is expressly specified.
Help texts never can mention all these details.
PLease note: The Boolean functions NOT() and XOR() are eligible for array-evaluation.

The given formula {=SUM((A1:A40>=C1)*(A1:A40<C2)*B1:B40)} (the curly brackets being automatically added for the display only) can work as intended if entered for array-evaluation (by Ctrl+Shift+Enter e.g.) because the one parameter expression of SUM() - that’s (A1:A40>=C1)*(A1:A40<C2)*B1:B40) - is evaluated in array-mode then. This isn’t done by the SUM() function itself, but in advance of passing the result to SUM.
Using the AND() function there in place of the arithmetic surrogate of multiplying the Boolean results autoconverted to ordinary numbers, will spoil this because AND() never will return an array. It always accumulates. Simply try it outside of the SUM() construct to clearly see what I mean.

If you insist on thorough distinction between Boolean and arithmetic results you are not directly supported by standard functions in the case. That’s simply a fact.

The simplest workarounds I can give are:
If((ArrayExpression1WithBooleanResults * ArrayExpression2WithBooleanResults)=1;TRUE();FALSE()) for AND and
If((ArrayExpression1WithBooleanResults + ArrayExpression2WithBooleanResults)>0;TRUE();FALSE()) for OR.
This way you also rely on the autoconversion Boolem ==>> Double, but you get the result in the same way as you get Boolean results otherwise.
My advice: Avoid the complication in this case. Soften strictness insofar as long as the functionality is clear.

In addition: The AND() functionality you also get using SUMPRODUCT(). (Riduculous name!) and there is the advantage that you don’t need the Ctrl+Shift when entering because the parameters of SUMPRODUCT() are specified ForceArray anyway.The OR() functionality is not available in a similar way.