Sum Product

M

Mike

I am trying to get a $ sum based on 2 text conditions, but
keep getting a #NUM! error. My formula works only when I
limit my search to certain rows. Ideally, here's how I'd
like it to work:

=SUMPRODUCT((O:O="Medical")*(R:R="Health Care")*(G:G))

The G column contains the $ figures that I'm trying to
add. But every so often the column contains a necessary
cell of text -- through trial and error, this fact seems
to be causing the problem.

Any ideas how I can fix this formula?
 
A

Andy B

Hi

You cannot use full columns in a SUMPRODUCT. Try this:
=SUMPRODUCT((O2:O65000="Medical")*(R2:R65000="Health Care")*(G2:G65000))
 
D

Dave Peterson

This version of =sumproduct() seems to be more forgiving:

=SUMPRODUCT(--(O2:O65000="Medical"),--(R2:R65000="Health Care"),(g2:g65000))

As long as you don't have any errors in the cells.

And you may want to try to keep that 65000 as small as possible. If you're only
using a few hundred rows, stop at 1000. (Big enough, but not too big. Watch
the difference in recalculation speed with giant numbers.)

You also may want to take a look at Data|pivottable. You can get some very nice
summaries with not much work.
 
M

Mike

Thank you Dave and Andy. Yesterday I kept poking around
the internet and found a solution (which maybe
accomplishes the same as your formula, Dave?). I replaced
my last * with a comma, which somehow tells Excel to
ignore the text in column G and just add up the numbers.
And I didn't need 2 parentheses at the very end, which
confused me at first but makes sense when I study the
formula closely.

So my final formula looks like this (I used row 400
instead of 65000, but I think either would work OK):

=SUMPRODUCT((O2:O400="Medical")*(R2:R400="Health
Care"),G2:G400)

It's that little commma that did the trick. The end result
is the dollar figure sum I was looking for.
 
D

Dave Peterson

Glad you got it working.

I used parentheses around all the arguments in the sumproduct function just
because I think it makes it easier to read. (personal preference only)
 
Top