SUMPRODUCT troubleshooting

[CathB]

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, howeve
I've even tried testing the formula on the same worksheet, but to n
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 usin
something else (without creating extra working sheets or columns, a
it's not my spreadsheet to redesign - sigh) would be MUCH appreciated!

Thanks,
Cat

 

[Stefi]

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


„CathB†ezt írta:

 

[Bondi]

Hi Cath

This might work,

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


Regards,
Bondi

 

[Stefi]

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

 

[CathB]

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

 

[Dav]

=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

 

[Stefi]

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

 

[CathB]

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 tha
my data was clean enough for a SUMPRODUCT = (count of rows that apply)
but not clean enough for a SUMPRODUCT = sum(applicable numbers in colum
N).

There were cells in column N with comments such as "n/a". I've sinc
cleaned those up and the forumla works fine.

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

Cat