Sumproduct error

  • Thread starter Thread starter Sarah (OGI)
  • Start date Start date
S

Sarah (OGI)

I've got 2 worksheets in 1 workbook, one labelled PO and one labelled OCR.

In the PO worksheet, I've entered the following formula:
=SUMPRODUCT(--(OCR!A2:A65536=C4),--(OCR!D2:D65536=A1),--(OCR!V2:V65536))

It's only returning a value of £15,674, but it should be returning a value
of £251,537 (when checking the data manually).

I must be missing something with my formula - is there an obvious error?

Thanks
 
Perhaps C4 or A1 contain numbers, but in the ranges some of the
numbers are actually text values (or vice versa). Or perhaps you have
text and some of the values in the ranges have extra spaces (which you
can't see, so it looks like they should match).

Hope this helps.

Pete
 
Sarah,

There's nothing wrong with the formula so it must be data problems. Put this
in w2
and drag down and it should evaluate as TRUE for your numbers. If it
evaluates as false then there not numbers

=isnumber(V2)

Mike
 
Thanks, Mike!

Using your idea, it appears that the culprit was actually column A - a
column of 4-digit values, but a mixture of numbers and text. I've changed
these to integers and it works fine now!!

Many thanks!
 
Back
Top