asked on

SUMPRODUCT Formula that ignores non-numeric values in the summary column

In the formula below, Column L contains a mixture of numbers and text. Is there a way to write the formula so that it doesn't bug?

Thanks,
John

=SUMPRODUCT((D7:D2500=I4)*(J7:J2500<L2)*L7:L2500)

Open in new window

jppinto

Can you post a sample sheet please?

jppinto

Please try it with the SUMIFS function, like this:

=SUMIFS(L7:L2500;D7:D2500;I4;J7:J2500;"<L2")

It would help to see your file to check the content of the used columns.

jppinto

SOLUTION

SiddharthRout

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ardhendu Sarangi

Have you tried using a double unary?

=SUMPRODUCT(--(D7:D2500=I4)*(J7:J2500<L2)*(L7:L2500))

ASKER CERTIFIED SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John Carney

ASKER

Hi Barry, thanks. I love it when it's so simple. I never knew that you didn't need the last asterisk. Sid, I chose Barry's answer over yours even though yours works because I always seem to forget CTL +Shift + Enter when editing the formula manually.

Thanks,
John

jppinto

You accepted two solutions without giving me feedback over mine that was the first to be posted! Did you at least tryed my formula?

SiddharthRout

>>>Sid, I chose Barry's answer over yours even though yours works because I always seem to forget CTL +Shift + Enter when editing the formula manually.

gabrielPennyback: No Worries :)

jppinto

Still waiting for feedback...