sumproduct logic

[mark1]

I have this formula, entered as an array:

=SUMPRODUCT((A9:A7250)*(IF((B9:B7250>H9)+(C9:C7250>H9)+
(D97250>H9)+(E9:E7250>H9)+(F9:F7250>H9),1,0)))

I understand that the + sign acts as "OR", but even though
the formula works, I can't quite get the logic. To me it
evaluates each column and assigns either a one or zero to
each cell within that column. Then the + sign adds the
corresponding cells from each column up. So you may have
values that are greater than 1. However, I don't know how
the formula moves from there. How does the IF know
whether to give it a one or zero? I mean I know it's
looking at each value and if it's greater than 0, it
assigns it a value of 1. But how does it know that I want
values greater than zero? Where is that in the formula?

 

[Arvi Laanemets]

Hi

You have there the formula
=SUMPRODUCT((SumRange)*(LogicalExpression(ExpressionRange))

The LogicalExpression there retuns TRUE OR FALSE for every processed row. So
you have
A9*(TRUEorFALSE)
+
A10*(TRUEorFALSE)
+
....
+
A7250*(TRUEorFALSE)

In Excel, when TRUE or FALSE are multiplied with a number, they behave as
numbers 1 or 0. So for every row, we got 0 when condition was false and
value from column A, when condition was true. And those results are summed.

With part of formula checking for condition is it differently. Let us
analyse the expression
(B9:B7250>H9)+(C9:C7250>H9)+(D97250>H9)+(E9:E7250>H9)+(F9:F7250>H9)

Here we have
LogicalExpr1+LogicalExpr2+LogicalExpr3+LogicalExpr4+LogicalExpr5
i.e. all components are logical values. So when p.e. all 5 components return
TRUE, the result is TRUE, not 5. And when there is at least one component
returning TRUE, the expression also returns TRUE. The FALSE is returned
only, when all 5 conditions are FALSE.

It looks like the formula will work without IF. Try this

=SUMPRODUCT((A9:A7250)*((B9:B7250>H9)+(C9:C7250>H9)+(D97250>H9)+(E9:E7250>
H9)+(F9:F7250>H9)))