sumproduct

[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

 

[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

 

[cjjoo]

tks for the advice....in the criteria when i was "looking" for the "45"
, actually i am searching for inputs that are 45 (numeric data ) and in
K1:K10000 , it is the column that i want to sum based on the criteria
that i have set and they are numeric. So does that mean that i can omit
the -- for these two conditions?

 

[Jerry W. Lewis]

Yes and no, if column K is already numeric and the 45 in column B is
numeric instead of text, then you should be using

=Sumproduct(--(A1:A10001="texas"),--(B1:B10001=45),--(C1:C10001="happy"),K1:K10001)

range=value is a logical expression that does still need to be coerced,
but value should be the value you are looking for (text or numeric).

With this change, the formula should work unless you have error values
in A1:C10001 or K1:K10001. If you do have error values there, then you
need to disclose which columns they are in so we can modify the formula
to ignore them.

Jerry