Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing based on a range of criteria | Excel Discussion (Misc queries) | |||
Summing a rolling range of cells | Excel Discussion (Misc queries) | |||
Summing a range based on value criteria | Excel Discussion (Misc queries) | |||
Summing cells in a range that has #VALUE! | Excel Discussion (Misc queries) | |||
summing part of cells in a range | Excel Discussion (Misc queries) |