Solved

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

Posted on 2011-05-04
Medium Priority
531 Views
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)
``````
0
Question by:gabrielPennyback

LVL 33

Expert Comment

ID: 35691812
Can you post a sample sheet please?
0

LVL 33

Expert Comment

ID: 35691845
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
0

LVL 30

Assisted Solution

SiddharthRout earned 400 total points
ID: 35691885
One more way.

=SUM(IF((D7:D2500=I4),IF((J7:J2500<L2),L7:L2500)))

After entering the formula, press CTL +Shift + Enter

Sid
0

LVL 20

Expert Comment

ID: 35692229
Have you tried using a double unary?

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

LVL 50

Accepted Solution

barry houdini earned 1600 total points
ID: 35692316
That won't work, Arhendu, you get errors because you are multiplying by text.SUMPRODUCT doesn't need * for the sum range, swap the last * for a comma, i.e.

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

Any text in L7:L2500 will be ignored

regards, barry
0

LVL 1

Author Closing Comment

ID: 35693477
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
0

LVL 33

Expert Comment

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

LVL 30

Expert Comment

ID: 35695489
>>>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 :)
0

LVL 33

Expert Comment

ID: 35709448
Still waiting for feedback...
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
###### Suggested Courses
Course of the Month14 days, 19 hours left to enroll