Summing a range of cells with criteria

  • Thread starter Thread starter Fredrated
  • Start date Start date
F

Fredrated

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
 
I'd use the =sumif() function:

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

Notice I changed the range to rows 1:341.
 
Fredrated said:
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
Click to expand...

Try this:

http://www.contextures.on.ca/xlFunctions01.html#SumIf
 
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
 
Make sure that the 0 or 1 in column Y is actually a number and not text that
looks like a number.
 
That's a good guess.
However, Format>cells says they are formatted as numbrs.

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

Fred
 
=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).
 
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.
 
Back
Top