Summing a range of cells with criteria
 

I am trying to use dsum(range,field,criteria) to sum values in 1 column when
another column equals 1, but I cannot understand from the help how the
criteria is determined from a range of cells.

from help: "Field indicates which column is used in the function"
But several columns are used in the function, so what does this mean?

One attempt:
=DSUM(R2:R340,25,Y1:Y341)
This is an attempt to add the values in column R rows 2 to 340 when column
Y(i.e., 25) for a given row is equal to 1. I placed a 1 into cell Y341.

Also tried, doesn't work:
=DSUM(R2:R340,18,Y1:Y341)
where 18 is column R, 'used in the function'.

However, this doesn't work, and I can't figure out from the help examples
exactly what the 'field' is or how criteria is to be expressed as a range of
cells.

Any help is appreciated, thanks.

Fred

Summing a range of cells with criteria
 

I'd use the =sumif() function:

=sumif(y1:y341,1,r1:r341)

Notice I changed the range to rows 1:341.



Fredrated wrote:

I am trying to use dsum(range,field,criteria) to sum values in 1 column when
another column equals 1, but I cannot understand from the help how the
criteria is determined from a range of cells.

from help: "Field indicates which column is used in the function"
But several columns are used in the function, so what does this mean?

One attempt:
=DSUM(R2:R340,25,Y1:Y341)
This is an attempt to add the values in column R rows 2 to 340 when column
Y(i.e., 25) for a given row is equal to 1. I placed a 1 into cell Y341.

Also tried, doesn't work:
=DSUM(R2:R340,18,Y1:Y341)
where 18 is column R, 'used in the function'.

However, this doesn't work, and I can't figure out from the help examples
exactly what the 'field' is or how criteria is to be expressed as a range of
cells.

Any help is appreciated, thanks.

Fred

--

Dave Peterson

Summing a range of cells with criteria
 

Fredrated wrote:
I am trying to use dsum(range,field,criteria) to sum values in 1 column when
another column equals 1, but I cannot understand from the help how the
criteria is determined from a range of cells.

from help: "Field indicates which column is used in the function"
But several columns are used in the function, so what does this mean?

One attempt:
=DSUM(R2:R340,25,Y1:Y341)
This is an attempt to add the values in column R rows 2 to 340 when column
Y(i.e., 25) for a given row is equal to 1. I placed a 1 into cell Y341.

Also tried, doesn't work:
=DSUM(R2:R340,18,Y1:Y341)
where 18 is column R, 'used in the function'.

However, this doesn't work, and I can't figure out from the help examples
exactly what the 'field' is or how criteria is to be expressed as a range of
cells.

Any help is appreciated, thanks.

Fred


Try this:

http://www.contextures.on.ca/xlFunctions01.html#SumIf

Summing a range of cells with criteria
 

Sounds good, but for reasons I don't understand it doesn't seem to work.

I used (in cell Y350)
=SUMIF(Y2:Y340,Y341,R2:R340) (Y341 = 1) and also tried
=SUMIF(Y2:Y340,1,R2:R340)
R2 to R340 contains acres of land, so all values are greater than or equal
to 0.
Column Y contains 0 or 1 depending on characteristics of the land in column R.
Many rows have a 1 in Y and acres in R

But the function only returns 0 in the formula cell.

????????

Fred


"Glenn" wrote:

Fredrated wrote:
I am trying to use dsum(range,field,criteria) to sum values in 1 column when
another column equals 1, but I cannot understand from the help how the
criteria is determined from a range of cells.

from help: "Field indicates which column is used in the function"
But several columns are used in the function, so what does this mean?

One attempt:
=DSUM(R2:R340,25,Y1:Y341)
This is an attempt to add the values in column R rows 2 to 340 when column
Y(i.e., 25) for a given row is equal to 1. I placed a 1 into cell Y341.

Also tried, doesn't work:
=DSUM(R2:R340,18,Y1:Y341)
where 18 is column R, 'used in the function'.

However, this doesn't work, and I can't figure out from the help examples
exactly what the 'field' is or how criteria is to be expressed as a range of
cells.

Any help is appreciated, thanks.

Fred


Try this:

http://www.contextures.on.ca/xlFunctions01.html#SumIf

Summing a range of cells with criteria
 

Make sure that the 0 or 1 in column Y is actually a number and not text that
looks like a number.


Fredrated wrote:
Sounds good, but for reasons I don't understand it doesn't seem to work.

I used (in cell Y350)
=SUMIF(Y2:Y340,Y341,R2:R340) (Y341 = 1) and also tried
=SUMIF(Y2:Y340,1,R2:R340)
R2 to R340 contains acres of land, so all values are greater than or equal
to 0.
Column Y contains 0 or 1 depending on characteristics of the land in column R.
Many rows have a 1 in Y and acres in R

But the function only returns 0 in the formula cell.

????????

Fred


"Glenn" wrote:

Fredrated wrote:
I am trying to use dsum(range,field,criteria) to sum values in 1 column when
another column equals 1, but I cannot understand from the help how the
criteria is determined from a range of cells.

from help: "Field indicates which column is used in the function"
But several columns are used in the function, so what does this mean?

One attempt:
=DSUM(R2:R340,25,Y1:Y341)
This is an attempt to add the values in column R rows 2 to 340 when column
Y(i.e., 25) for a given row is equal to 1. I placed a 1 into cell Y341.

Also tried, doesn't work:
=DSUM(R2:R340,18,Y1:Y341)
where 18 is column R, 'used in the function'.

However, this doesn't work, and I can't figure out from the help examples
exactly what the 'field' is or how criteria is to be expressed as a range of
cells.

Any help is appreciated, thanks.

Fred

Try this:

http://www.contextures.on.ca/xlFunctions01.html#SumIf

Summing a range of cells with criteria
 

That's a good guess.
However, Formatcells says they are formatted as numbrs.

But in case, I tried =SUMIF(Y2:Y340,"1",R2:R340), but still no luck.

Fred

"Glenn" wrote:

Make sure that the 0 or 1 in column Y is actually a number and not text that
looks like a number.


Fredrated wrote:
Sounds good, but for reasons I don't understand it doesn't seem to work.

I used (in cell Y350)
=SUMIF(Y2:Y340,Y341,R2:R340) (Y341 = 1) and also tried
=SUMIF(Y2:Y340,1,R2:R340)
R2 to R340 contains acres of land, so all values are greater than or equal
to 0.
Column Y contains 0 or 1 depending on characteristics of the land in column R.
Many rows have a 1 in Y and acres in R

But the function only returns 0 in the formula cell.

????????

Fred


"Glenn" wrote:

Fredrated wrote:
I am trying to use dsum(range,field,criteria) to sum values in 1 column when
another column equals 1, but I cannot understand from the help how the
criteria is determined from a range of cells.

from help: "Field indicates which column is used in the function"
But several columns are used in the function, so what does this mean?

One attempt:
=DSUM(R2:R340,25,Y1:Y341)
This is an attempt to add the values in column R rows 2 to 340 when column
Y(i.e., 25) for a given row is equal to 1. I placed a 1 into cell Y341.

Also tried, doesn't work:
=DSUM(R2:R340,18,Y1:Y341)
where 18 is column R, 'used in the function'.

However, this doesn't work, and I can't figure out from the help examples
exactly what the 'field' is or how criteria is to be expressed as a range of
cells.

Any help is appreciated, thanks.

Fred

Try this:

http://www.contextures.on.ca/xlFunctions01.html#SumIf

Summing a range of cells with criteria
 

=sumif() will match both 1 and "1".

So I don't think that's your problem.

My guess is that R2:R340 are not really numbers.

Remember that it's not enough to just reformat the range to General (or
Number). That just means that with the next change, the value will be a number.

If you put:
=count(r2:r340)
and
=counta(r2:r34)

in two cells, do you get the same number back?

=Count() will count the numbers (not including text that looks like numbers).
=counta() will count any non-empty cells--including those with formulas that
evaluate to ""

If your values are really text numbers, you can select an empty cell.
Edit|copy

select the range to fix (R2:R340)
edit|Paste special|click Add and Values

Then check the cell with the formula.

If that doesn't help, make sure you're pointing at the correct ranges. And make
sure that the 1's in column Y are plain old 1's--not leading/trailing spaces (or
numbers that are formatted to show no decimals, but the value is something like
1.1 or .98).


Fredrated wrote:

That's a good guess.
However, Formatcells says they are formatted as numbrs.

But in case, I tried =SUMIF(Y2:Y340,"1",R2:R340), but still no luck.

Fred

"Glenn" wrote:

Make sure that the 0 or 1 in column Y is actually a number and not text that
looks like a number.


Fredrated wrote:
Sounds good, but for reasons I don't understand it doesn't seem to work.

I used (in cell Y350)
=SUMIF(Y2:Y340,Y341,R2:R340) (Y341 = 1) and also tried
=SUMIF(Y2:Y340,1,R2:R340)
R2 to R340 contains acres of land, so all values are greater than or equal
to 0.
Column Y contains 0 or 1 depending on characteristics of the land in column R.
Many rows have a 1 in Y and acres in R

But the function only returns 0 in the formula cell.

????????

Fred


"Glenn" wrote:

Fredrated wrote:
I am trying to use dsum(range,field,criteria) to sum values in 1 column when
another column equals 1, but I cannot understand from the help how the
criteria is determined from a range of cells.

from help: "Field indicates which column is used in the function"
But several columns are used in the function, so what does this mean?

One attempt:
=DSUM(R2:R340,25,Y1:Y341)
This is an attempt to add the values in column R rows 2 to 340 when column
Y(i.e., 25) for a given row is equal to 1. I placed a 1 into cell Y341.

Also tried, doesn't work:
=DSUM(R2:R340,18,Y1:Y341)
where 18 is column R, 'used in the function'.

However, this doesn't work, and I can't figure out from the help examples
exactly what the 'field' is or how criteria is to be expressed as a range of
cells.

Any help is appreciated, thanks.

Fred

Try this:

http://www.contextures.on.ca/xlFunctions01.html#SumIf



--

Dave Peterson

Summing a range of cells with criteria
 

What matters is not whether they are FORMATTED as numbers, because that
affects only the display, but whether the contents ARE numbers.
Check with the formulae =ISNUMBER(Y2), =ISTEXT(Y2), and similarly
=ISNUMBER(R2), and =ISTEXT(R2)
Similarly try =Y2=1 to see whether that returns true or false.
--
David Biddulph

"Fredrated" wrote in message
...
That's a good guess.
However, Formatcells says they are formatted as numbrs.

But in case, I tried =SUMIF(Y2:Y340,"1",R2:R340), but still no luck.

Fred

"Glenn" wrote:

Make sure that the 0 or 1 in column Y is actually a number and not text
that
looks like a number.


Fredrated wrote:
Sounds good, but for reasons I don't understand it doesn't seem to
work.

I used (in cell Y350)
=SUMIF(Y2:Y340,Y341,R2:R340) (Y341 = 1) and also tried
=SUMIF(Y2:Y340,1,R2:R340)
R2 to R340 contains acres of land, so all values are greater than or
equal
to 0.
Column Y contains 0 or 1 depending on characteristics of the land in
column R.
Many rows have a 1 in Y and acres in R

But the function only returns 0 in the formula cell.

????????

Fred


"Glenn" wrote:

Fredrated wrote:
I am trying to use dsum(range,field,criteria) to sum values in 1
column when
another column equals 1, but I cannot understand from the help how
the
criteria is determined from a range of cells.

from help: "Field indicates which column is used in the function"
But several columns are used in the function, so what does this mean?

One attempt:
=DSUM(R2:R340,25,Y1:Y341)
This is an attempt to add the values in column R rows 2 to 340 when
column
Y(i.e., 25) for a given row is equal to 1. I placed a 1 into cell
Y341.

Also tried, doesn't work:
=DSUM(R2:R340,18,Y1:Y341)
where 18 is column R, 'used in the function'.

However, this doesn't work, and I can't figure out from the help
examples
exactly what the 'field' is or how criteria is to be expressed as a
range of
cells.

Any help is appreciated, thanks.

Fred

Try this:

http://www.contextures.on.ca/xlFunctions01.html#SumIf