Sum Product

[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?

 

[Andy B]

Hi

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

 

[Mike]

Andy -- thanks for reply. I tried, but result is now
#VALUE!

My formula works when my cell range for columns O, R, and
G is limited to rows 2:23. (G2:G23 contains all $
figures.) BUT when I extend the range even one row beyond
(whether to row 24 or 65000 as in your example), the
formula doesn't work. (The significance of row 24 is that
G24 contains text, not a $ figure.) Maybe SUMPRODUCT just
won't work if column G has a mix of $ figures and text?

 

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

 

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

 

[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)