sumproduct

C

cjjoo

this is problem:


i got a database sheet and there are a lot of info in there.

Now, i want to use sumproduct to calculate the sum of A (e.g) in the

database based on a few criteria. the formula i use i
:Sumproduct(--(A1:A10001="texas"),--(B1:B10001="45"),--(C1:C10001="happy"),--(K1:K10001))
Is there any thing wrong with this formula? And can I set the range a
high as 10001? the return gives #value!


can anyone help me
 
J

Jerry W. Lewis

For this type of formula, you can use up to 65535=2^16-1 cells in a
column; 10001 is well within that.

The formula is valid, so the problem is with your data. Are there any
values in K1:K10001 that cannot be coerced into numbers (such as text
including alphabetic characters)? Are there any error values in the
referenced ranges?

Do you understand the function of -- in this formula? It is there to
force conversion of non-numeric to numeric data. For your conditions,
it is forcing TRUE|False data into ones and zeros. What kind of data do
you have in K1:K10001 that it is not already numbers but can be
converted into numbers? If type coercion is not needed, then omit the
-- on the last argument.

It would not produce a zero result instead of an error, but are you
really looking for a text value of "45" instead of a numeric value of 45
in B1:B10001 ?

Jerry
 
Top