R
Richard
Whats wrong.
{=SUM((Sheet1!A:A="*")*(Sheet1!B:B="\\\\\\CC\\\\\\")
*Sheet1!E:E)}
thanks in advance
{=SUM((Sheet1!A:A="*")*(Sheet1!B:B="\\\\\\CC\\\\\\")
*Sheet1!E:E)}
thanks in advance
J
JE McGimpsey
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).
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).
C
Chip Pearson
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
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
F
Frank Kabel
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)
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)
K
kfotedar
Frank:
A quick question,
I tend to use Sumproduct quite often in my excel models, but I don'
understand the "--" operator that you have used.
What does it exactly accomplish? Would like to add this to my grwoin
knowledge base of excel formula tips and tricks.
Kavi
A quick question,
I tend to use Sumproduct quite often in my excel models, but I don'
understand the "--" operator that you have used.
What does it exactly accomplish? Would like to add this to my grwoin
knowledge base of excel formula tips and tricks.
Kavi
J
JE McGimpsey
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.
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.
R
Ragdyer
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?
<<"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?
J
JE McGimpsey
Yes. From help:
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
rice>10 =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).
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
rice>10 =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).
R
RagDyer
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!
-------------------------------------------------------------------
Yes. From help:
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 TOTALrice>10 =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).
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!
-------------------------------------------------------------------
Yes. From help:
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
rice>10 =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).
J
JE McGimpsey
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...
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 D1
20 and E1:E20 contain imported data,with all column D values imported as numbers, but all column E values
imported as Text,
=SUMPRODUCT(D1
20,E1:E20)indeed returns zero, however
=SUMPRODUCT(--D1
20,--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...
R
RagDyer
This has been a personal issue with me for a while John, since I screwed up
royally, by revising some formulas from the "*" style to the unary.
<<"Not sure what you mean when you say "the unary returns a zero".">>
I've a datalist that keeps track of the volatile pricing of dyestuff.
The cost of production is in part dependant on the price of the dye at the
time of purchase, then equated to the date of it's use on various lots of
fabric.
The datalist is populated by either importing the prices from the vendors
web site, or being keyed in by the office, from faxes or snail mail.
Need I say more !
Mixed data values !
BUT ... that *DIDN'T* matter with this formula:
=SUMPRODUCT((A2:A200<=D1)*(B2:B200=F15)*C2:C200)
A = dates
B = dyestuff name
C = prices
BUT definitely *DID* matter with this formula:
=SUMPRODUCT(--(A2:A200<=D1),--(B2:B200=F15),C2:C200)
Felt very proud that we were able to reduce costs to such a great extent.
That is, until the accounting department started reconciling inventories!
<<"Bottom line: it's the responsibility of the spreadsheet designer to
ensure that inputs are of the proper form.">>
Very true.
But just like 9/11 ... until it happens the first time, you don't really
give it much thought.
--
Regards,
RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------
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...
royally, by revising some formulas from the "*" style to the unary.
<<"Not sure what you mean when you say "the unary returns a zero".">>
I've a datalist that keeps track of the volatile pricing of dyestuff.
The cost of production is in part dependant on the price of the dye at the
time of purchase, then equated to the date of it's use on various lots of
fabric.
The datalist is populated by either importing the prices from the vendors
web site, or being keyed in by the office, from faxes or snail mail.
Need I say more !
Mixed data values !
BUT ... that *DIDN'T* matter with this formula:
=SUMPRODUCT((A2:A200<=D1)*(B2:B200=F15)*C2:C200)
A = dates
B = dyestuff name
C = prices
BUT definitely *DID* matter with this formula:
=SUMPRODUCT(--(A2:A200<=D1),--(B2:B200=F15),C2:C200)
Felt very proud that we were able to reduce costs to such a great extent.
That is, until the accounting department started reconciling inventories!
<<"Bottom line: it's the responsibility of the spreadsheet designer to
ensure that inputs are of the proper form.">>
Very true.
But just like 9/11 ... until it happens the first time, you don't really
give it much thought.
--
Regards,
RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------
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 D1
20 and E1:E20 contain imported data,with all column D values imported as numbers, but all column E values
imported as Text,
=SUMPRODUCT(D1
20,E1:E20)indeed returns zero, however
=SUMPRODUCT(--D1
20,--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...
J
JE McGimpsey
RagDyer said:
For more on that, see .programming's thread on XL Math Error...
Warning - lots of heat, no light.
R
RagDyeR
<<"For more on that, see .programming's thread on XL Math Error..">>
That went right over my head.
Found nothing pertinent in Google.
--
Regards,
RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------
For more on that, see .programming's thread on XL Math Error...
Warning - lots of heat, no light.
That went right over my head.
Found nothing pertinent in Google.
--
Regards,
RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------
RagDyer said:
For more on that, see .programming's thread on XL Math Error...
Warning - lots of heat, no light.