Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct error
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct error
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 On Nov 11, 11:36*am, Sarah (OGI) wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct error
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 "Sarah (OGI)" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct error
For a well explained use of SUMPRODUCT look here http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- The Code Cage Team Regards, The Code Cage Team http://www.thecodecage.com ------------------------------------------------------------------------ The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=27930 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct error
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! "Mike H" wrote: 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 "Sarah (OGI)" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT Error | Excel Discussion (Misc queries) | |||
error in SUMPRODUCT | Excel Discussion (Misc queries) | |||
SUMPRODUCT #VALUE! Error | Excel Worksheet Functions | |||
Sumproduct value error | Excel Worksheet Functions | |||
#ref error with Sumproduct | Excel Worksheet Functions |