=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.

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

--

"JPS" wrote in message
...

=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.

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

"JPS" wrote in message
...

=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.

Hello JPS,

Perhaps try changing to an "array formula" like this

=SUM(IF(IF(Other!$J$2:$J$10000<"",Other!$J$2:$J$1 0000,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