If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
#NUM! error
Whats wrong.
{=SUM((Sheet1!A:A="*")*(Sheet1!B:B="\\\\\\CC\\\\\\ ") *Sheet1!E:E)} thanks in advance |
#2
|
|||
|
|||
#NUM! error
You can't use entire columns or rows in array formulae.
Try (array-entered): =SUM((Sheet1!A1:A65535="*")*(Sheet1!B1:B65535="\\\ \\\CC\\\\\\") *Sheet1!E1:E65535)} or (not array-entered, but an array formula none-the-less): =SUMPRODUCT(--(Sheet1!A1:A65535="*"), --(Sheet1!B1:B65535="\\\\\\CC\\\\\\"), Sheet1!E1:E65535) (not array-entered, but an array formula none-the-less). In article , "Richard" wrote: Whats wrong. {=SUM((Sheet1!A:A="*")*(Sheet1!B:B="\\\\\\CC\\\\\\ ") *Sheet1!E:E)} |
#3
|
|||
|
|||
#NUM! error
Richard,
You can't use entire column references in array formulas. Try =SUM((Sheet1!A1:A65535="*")*(Sheet1!B1:B65535="\\\ \\\CC\\\\\\")*S heet1!E1:E65535) or, better, adjust the ending row to a more reasonable value. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Richard" wrote in message ... Whats wrong. {=SUM((Sheet1!A:A="*")*(Sheet1!B:B="\\\\\\CC\\\\\\ ") *Sheet1!E:E)} thanks in advance |
#4
|
|||
|
|||
#NUM! error
Hi
try =SUMPRODUCT(--(Sheet1!A1:A1000="*"),--(Sheet1!B1:B1000="\\\\\\CC\\\\\\" ),sheet1!E1:E1000) Note: SUMPRODUCT does not accept ranges like A:A. Also if you want to test that column A contains ANY text (* as wildcard) use the formula =SUMPRODUCT(--(Sheet1!A1:A1000""),--(Sheet1!B1:B1000="\\\\\\CC\\\\\\" ),sheet1!E1:E1000) -- Regards Frank Kabel Frankfurt, Germany Richard wrote: Whats wrong. {=SUM((Sheet1!A:A="*")*(Sheet1!B:B="\\\\\\CC\\\\\\ ") *Sheet1!E:E)} thanks in advance |
#5
|
|||
|
|||
#NUM! error
Frank:
A quick question, I tend to use Sumproduct quite often in my excel models, but I don't understand the "--" operator that you have used. What does it exactly accomplish? Would like to add this to my grwoing knowledge base of excel formula tips and tricks. Kavir --- Message posted from http://www.ExcelForum.com/ |
#6
|
|||
|
|||
#NUM! error
SUMPRODUCT requires numeric arrays. the double unary minus converts the
True/False from comparison arguments to 1/0, respectively, first by convertint True to -1 and FALSE to 0, then using the second unary minus to negate the result of the first, producing 1/0. It's somewhat faster than using the * operator to multiply the arrays first, then pass them to SUMPRODUCT. It also preserves SUMPRODUCT's ability to ignore non-numeric values. In article , kfotedar wrote: I tend to use Sumproduct quite often in my excel models, but I don't understand the "--" operator that you have used. What does it exactly accomplish? Would like to add this to my grwoing knowledge base of excel formula tips and tricks. |
#7
|
|||
|
|||
#NUM! error
I wish I was knowledgeable enough to debate you guys over this issue.
"It also preserves SUMPRODUCT's ability to ignore non-numeric values." Do you mean by that John, that it returns zero instead of an error? So you could have a text numeric, or simply not have the value you're looking for in the searched range, and receive the same answer for either. Is that desirable? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "JE McGimpsey" wrote in message ... SUMPRODUCT requires numeric arrays. the double unary minus converts the True/False from comparison arguments to 1/0, respectively, first by convertint True to -1 and FALSE to 0, then using the second unary minus to negate the result of the first, producing 1/0. It's somewhat faster than using the * operator to multiply the arrays first, then pass them to SUMPRODUCT. It also preserves SUMPRODUCT's ability to ignore non-numeric values. In article , kfotedar wrote: I tend to use Sumproduct quite often in my excel models, but I don't understand the "--" operator that you have used. What does it exactly accomplish? Would like to add this to my grwoing knowledge base of excel formula tips and tricks. |
#8
|
|||
|
|||
#NUM! error
Yes. From help:
SUMPRODUCT treats array entries that are not numeric as if they were zeros. One example where it's desirable: A B 1 QUANTITY PRICE 2 Category 1 items: 3 1 10 4 2 12 5 Category 2 items: 6 3 8 7 2 9 8 Category 3 items: 9 7 14 10 3 21 11 TOTAL:Price10 =SUMPRODUCT(A1:A10,--(B1:B10=10),B1:B10) B11 will return 195 If instead you used =SUMPRODUCT(A1:A10*(B1:B10=10)*B1:B10) You'll either get #VALUE! or 0 with a circular reference error, depending on XL version (there's a bug). In article , "Ragdyer" wrote: I wish I was knowledgeable enough to debate you guys over this issue. "It also preserves SUMPRODUCT's ability to ignore non-numeric values." Do you mean by that John, that it returns zero instead of an error? So you could have a text numeric, or simply not have the value you're looking for in the searched range, and receive the same answer for either. Is that desirable? |
#9
|
|||
|
|||
#NUM! error
You used a good example.g
But my point is, if B1:B10 is populated with imported data, and that data is numeric text, then the unary returns a zero. That doesn't tell you if the conditions were *not* met, or if the data is (was) "contaminated". -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit! ------------------------------------------------------------------- "JE McGimpsey" wrote in message ... Yes. From help: SUMPRODUCT treats array entries that are not numeric as if they were zeros. One example where it's desirable: A B 1 QUANTITY PRICE 2 Category 1 items: 3 1 10 4 2 12 5 Category 2 items: 6 3 8 7 2 9 8 Category 3 items: 9 7 14 10 3 21 11 TOTAL:Price10 =SUMPRODUCT(A1:A10,--(B1:B10=10),B1:B10) B11 will return 195 If instead you used =SUMPRODUCT(A1:A10*(B1:B10=10)*B1:B10) You'll either get #VALUE! or 0 with a circular reference error, depending on XL version (there's a bug). In article , "Ragdyer" wrote: I wish I was knowledgeable enough to debate you guys over this issue. "It also preserves SUMPRODUCT's ability to ignore non-numeric values." Do you mean by that John, that it returns zero instead of an error? So you could have a text numeric, or simply not have the value you're looking for in the searched range, and receive the same answer for either. Is that desirable? |
#10
|
|||
|
|||
#NUM! error
Not sure what you mean when you say "the unary returns a zero".
First, if you're importing data, it's unlikely that you'll have a structure like the one I suggested - it's far more likely that a table of numeric values is intended to be imported as numbers, whether they import as numbers or text. If D120 and E1:E20 contain imported data, with all column D values imported as numbers, but all column E values imported as Text, =SUMPRODUCT(D120,E1:E20) indeed returns zero, however =SUMPRODUCT(--D120,--E1:E20) returns the appropriate value. Bottom line: it's the responsibility of the spreadsheet designer to ensure that inputs are of the proper form. In a properly designed application, you shouldn't have to worry about data type, or to cripple valid functions to compensate for possible input errors. And of course, in XL03 and XL04, at least, you get a smart button popping up whenever you have "text numbers", offering to convert them... In article , "RagDyer" wrote: But my point is, if B1:B10 is populated with imported data, and that data is numeric text, then the unary returns a zero. That doesn't tell you if the conditions were *not* met, or if the data is (was) "contaminated". |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Upgrade from exchange 2000 to exchange 2003 error | John Landis | Installation & Setup | 2 | June 18th, 2004 02:28 PM |
Upgrade from Exchange 2000 to 2003 error | John Landis | Installation & Setup | 0 | June 17th, 2004 08:26 PM |
Custom Error Messages | DMc2004 | Database Design | 4 | June 11th, 2004 11:16 PM |
Product Key for Office XP | P.G.Indiana | Setup, Installing & Configuration | 1 | June 7th, 2004 03:22 AM |
Productkey problem when installing office 2003 on network | Stefan Schreurs | Setup, Installing & Configuration | 1 | June 1st, 2004 11:16 PM |