Formula to summarize the multiplication results of two columns if criteria met

Question

This First formula make half of the work:

=SUMPRODUCT(--ISNUMBER(FIND("X",$A$1:$A$3)),$B$1:$B$3,$E$1:$E$3)

The result intended to reach in the example is 60, the given formula only works with one column, in this case B, so C and D is missed.

A second formula summarize what is needed in the above one.

=SUMPRODUCT((ISNUMBER(SEARCH("X",$A$1:$A$3)))*($B$1:D3))

These 2 are the closest ways I can reach, can they be mixed?

Answer 1

Yes, they can be mixed so

=SUMPRODUCT(ISNUMBER(SEARCH("x",A1:A3))*B1:D3*E1:E3)

will give the right answer because it multiplies corresponding elements of all three arrays.

The arrays in SUMPRODUCT all have to have the same dimensions so this would give a #VALUE error because the second array has three columns while the first and third only have one

=SUMPRODUCT(ISNUMBER(SEARCH("x",A1:A3)),B1:D3,E1:E3)