Dear all,

I am using SUMPRODUCT to use 2 criteria, in column A (criteria="M"),
then column B (criteria="R"), to then sum the totals in column N.

=SUMPRODUCT("'Dashboard'!A9:A500,M","'Dashboard'!B 9:B500,R",'Dashboard'!N9:N500)

I keep getting "VALUE!" 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: http://www.excelforum.com/member.php...o&userid=34291
View this thread: http://www.excelforum.com/showthread...hreadid=540573

=SUMPRODUCT(--(A9:A500="M"),--(B9:B500="R"),N9:N500)
Regards,
Stefi


€žCathB€� ezt Ã*rta:


Dear all,

I am using SUMPRODUCT to use 2 criteria, in column A (criteria="M"),
then column B (criteria="R"), to then sum the totals in column N.

=SUMPRODUCT("'Dashboard'!A9:A500,M","'Dashboard'!B 9:B500,R",'Dashboard'!N9:N500)

I keep getting "VALUE!" 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: http://www.excelforum.com/member.php...o&userid=34291
View this thread: http://www.excelforum.com/showthread...hreadid=540573

Sorry, I also tested it on the same sheet and forgot sheet name:

=SUMPRODUCT(--(Dashboard!A9:A500="M"),--(Dashboard!B9:B500="R"),Dashboard!N9:N500)

Regards,
Stefi

Thanks for your help guys, unfortunately I am now getting a result of
"0"; 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: http://www.excelforum.com/member.php...o&userid=34291
View this thread: http://www.excelforum.com/showthread...hreadid=540573

=SUMPRODUCT((Dashboard!A9:A500="M")*(Dashboard!B9: B500="R")*(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: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=540573

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 "n/a". 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: http://www.excelforum.com/member.php...o&userid=34291
View this thread: http://www.excelforum.com/showthread...hreadid=540573

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.

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 Cath

This might work,

=SUMPRODUCT(('Dashboard'!A9:A500="M")*('Dashboard' !B9:B500="R")*('Dashboard'!N9:*N500))


Regards,
Bondi