SUMPRODUCT with If condition

[JPS]

=SUMPRODUCT(--(Other!$G$2:$G$10000>=C$4),--(Other!$O$2:$O$10000 =
""),Other!$H$2:$H$10000)


The formula above works fine. However, I need to modify it to do the
following:

If Column J on the "Other" worksheet has a value, I need my match to refer
to column J. However, if column J is blank, I need the match to refer to
column G as it is doing above.

 

[PCLIVE]

Maybe this:

=SUMPRODUCT(--(Other!$J$2:$J$10000<>""),Other!$J$2:$J$10000>=C$4),--(Other!$O$2:$O$10000
=
""),Other!$H$2:$H$10000) +
SUMPRODUCT(--(Other!$J$2:$J$10000=""),Other!$G$2:$G$10000>=C$4),--(Other!$O$2:$O$10000
=
""),Other!$H$2:$H$10000)

HTH,
Paul

 

[Shane Devenshire]

Hi,

What column J? There is no column J in your formula. There is not MATCH in
your formula, could you be a little bit more specific.

Cheers,
Shane Devenshire

 

[barry houdini]

Hello JPS,

Perhaps try changing to an "array formula" like this

=SUM(IF(IF(Other!$J$2:$J$10000<>"",Other!$J$2:$J$10000,Other!$G$2:$G
$10000)>=C$4,IF(Other!$O$2:$O$10000="",Other!$H$2:$H$10000)))

Needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces
appear around the formula in the formula bar