SUMPRODUCT troubleshooting

  • Thread starter Thread starter CathB
  • Start date Start date
C

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
 
=SUMPRODUCT(--(A9:A500="M"),--(B9:B500="R"),N9:N500)
Regards,
Stefi


„CathB†ezt írta:
 
Hi Cath

This might work,

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


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
 
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.
Click to expand...

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 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
 
Back
Top