SUMPRODUCT troubleshooting

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'!B9: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

=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'!B9: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
>
>

Hi Cath

This might work,

=3DSUMPRODUCT(('Dashboard'!A9:A500=3D"M")*('Dashboard'!B9:B500=3D"R")*('Das=
hboard'!N9:=ADN500))


Regards,
Bondi

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

=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

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

> 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