software

Dear all,

I am using SUMPRODUCT to use 2 criteria, in column A (criteria=quot;Mquot;),
then column B (criteria=quot;Rquot;), to then sum the totals in column N.

=SUMPRODUCT(quot;'Dashboard'!A9:A500,Mquot;,quot;'Dashboard'!B 9:B500,Rquot;,'Dashboard'!N9:N500)

I keep getting quot;VALUE!quot; as an error message.

NB - The data is on one worksheet and the formula on another, however
I've even tried testing the formula on the same worksheet, but to no
avail. Please help, I'm at a loss as to what I'm doing wrong...

Alternatively - any suggestions on how I can acheieve what I want using
something else (without creating extra working sheets or columns, as
it's not my spreadsheet to redesign - sigh) would be MUCH appreciated!

Thanks,
Cath--
CathB
------------------------------------------------------------------------
CathB's Profile: www.excelforum.com/member.php...oamp;userid=34291
View this thread: www.excelforum.com/showthread...hreadid=540573=SUMPRODUCT(--(A9:A500=quot;Mquot;),--(B9:B500=quot;Rquot;),N9:N500)
Regards,
Stefi?athB??ezt ?rta:

gt;
gt; Dear all,
gt;
gt; I am using SUMPRODUCT to use 2 criteria, in column A (criteria=quot;Mquot;),
gt; then column B (criteria=quot;Rquot;), to then sum the totals in column N.
gt;
gt; =SUMPRODUCT(quot;'Dashboard'!A9:A500,Mquot;,quot;'Dashboard'!B 9:B500,Rquot;,'Dashboard'!N9:N500)
gt;
gt; I keep getting quot;VALUE!quot; as an error message.
gt;
gt; NB - The data is on one worksheet and the formula on another, however
gt; I've even tried testing the formula on the same worksheet, but to no
gt; avail. Please help, I'm at a loss as to what I'm doing wrong...
gt;
gt; Alternatively - any suggestions on how I can acheieve what I want using
gt; something else (without creating extra working sheets or columns, as
gt; it's not my spreadsheet to redesign - sigh) would be MUCH appreciated!
gt;
gt; Thanks,
gt; Cath
gt;
gt;
gt; --
gt; CathB
gt; ------------------------------------------------------------------------
gt; CathB's Profile: www.excelforum.com/member.php...oamp;userid=34291
gt; View this thread: www.excelforum.com/showthread...hreadid=540573
gt;
gt;

Hi Cath

This might work,

=SUMPRODUCT(('Dashboard'!A9:A500=quot;Mquot;)*('Dashboard' !B9:B500=quot;Rquot;)*('Dashboard'!N9:*N500))Regards,
BondiSorry, I also tested it on the same sheet and forgot sheet name:

=SUMPRODUCT(--(Dashboard!A9:A500=quot;Mquot;),--(Dashboard!B9:B500=quot;Rquot;),Dashboard!N9:N500)

Regards,
Stefi
Thanks for your help guys, unfortunately I am now getting a result of
quot;0quot;; i.e. none of the rows fit appear to fit the criteria, though I
know they do.

I've used the same data set and critera columns in other formula on my
spreadsheet - even using the SUMPRODUCT function - and those other
formula yielf correct results, so I know the data is clean and not
causing my problem.

Currently, I am able use the SUMPRODUCT function to *count* the number
of rows, within the multiple criteria set in column A and B, but I
simply can't get the function to *sum* the figures in column N, given
criteria set in columns A and B.

Does anyone have any further thoughts? (Thank you!!!)
Cath--
CathB
------------------------------------------------------------------------
CathB's Profile: www.excelforum.com/member.php...oamp;userid=34291
View this thread: www.excelforum.com/showthread...hreadid=540573
=SUMPRODUCT((Dashboard!A9:A500=quot;Mquot;)*(Dashboard!B9: B500=quot;Rquot;)*(Dashboard!N9:N500))

On my computer replacing the commas with * makes things work, the last
range is now bracketed. I think it is something to do with the
conditions being returned as true and false, but multiplying them has
the effect of turning them into 1s or 0s

Regards

Dav--
Dav
------------------------------------------------------------------------
Dav's Profile: www.excelforum.com/member.php...oamp;userid=27107
View this thread: www.excelforum.com/showthread...hreadid=540573
gt; Currently, I am able use the SUMPRODUCT function to *count* the number
gt; of rows, within the multiple criteria set in column A and B, but I
gt; simply can't get the function to *sum* the figures in column N, given
gt; criteria set in columns A and B.

My formula should do exactly what you want (sum figures in column N, given
criteria set in columns A and B.)
Could you prepare a little example!

Stefi
Hi all,

I've finally got the formula to work! Hurrah! Thanks Dav.

In case anyone wanted to know the end of the story: it turns out that
my data was clean enough for a SUMPRODUCT = (count of rows that apply),
but not clean enough for a SUMPRODUCT = sum(applicable numbers in column
N).

There were cells in column N with comments such as quot;n/aquot;. I've since
cleaned those up and the forumla works fine.

It pains me that I will still have to amend the reporting data that
gets to me every month in order to process it, as the formula can't
cope with cells that clearly aren't numbers; but at least my
spreadsheet works! Joy! Thank you very much all!

Cath--
CathB
------------------------------------------------------------------------
CathB's Profile: www.excelforum.com/member.php...oamp;userid=34291
View this thread: www.excelforum.com/showthread...hreadid=540573