?
Solved

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

Posted on 2011-05-04
9
Medium Priority
?
531 Views
Last Modified: 2012-08-13
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

0
Comment
Question by:gabrielPennyback
9 Comments
 
LVL 33

Expert Comment

by:jppinto
ID: 35691812
Can you post a sample sheet please?
0
 
LVL 33

Expert Comment

by:jppinto
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

by:SiddharthRout
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 20

Expert Comment

by:Ardhendu Sarangi
ID: 35692229
Have you tried using a double unary?


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

Accepted Solution

by:
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

by:gabrielPennyback
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

by:jppinto
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

by:SiddharthRout
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

by:jppinto
ID: 35709448
Still waiting for feedback...
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question