If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
SUMIF() to add cells in non-contiguous ranges? (Excel 2003)
I have a bunch of columns with dollar values. I want to add the sum of all
the values for rows that meet a single condition. If possible, I'd like to create a named a range that includes all the non-contiguous dollar value columns and use a single SUMIF(). In my test, I named this range DOLLARS. The formula =SUM(DOLLARS) does work fine and adds up ALL the values. To test the SUMIF(), I set up a column (named range "decision") with yes/no values. I get a #VALUE error when I try the formula =SUMIF(decision,"=yes",dollars). I looked at some of the posts for array functions and I can't really tell if an array formula would fix this problem. Is this possible to do as a single function, or do I have to have something like this: =SUMIF(decision,"=yes",dollars1)+SUMIF(decision,"= yes",dollars2)... Thanks for your help. -- Ann Scharpf |
#2
|
|||
|
|||
SUMIF() to add cells in non-contiguous ranges? (Excel 2003)
In my test, I named this range DOLLARS.
What are the individual range addresses that make up DOLLARS? I set up a column (named range "decision") with yes/no What is the range address that makes up DECISION? -- Biff Microsoft Excel MVP "Ann Scharpf" wrote in message ... I have a bunch of columns with dollar values. I want to add the sum of all the values for rows that meet a single condition. If possible, I'd like to create a named a range that includes all the non-contiguous dollar value columns and use a single SUMIF(). In my test, I named this range DOLLARS. The formula =SUM(DOLLARS) does work fine and adds up ALL the values. To test the SUMIF(), I set up a column (named range "decision") with yes/no values. I get a #VALUE error when I try the formula =SUMIF(decision,"=yes",dollars). I looked at some of the posts for array functions and I can't really tell if an array formula would fix this problem. Is this possible to do as a single function, or do I have to have something like this: =SUMIF(decision,"=yes",dollars1)+SUMIF(decision,"= yes",dollars2)... Thanks for your help. -- Ann Scharpf |
#3
|
|||
|
|||
SUMIF() to add cells in non-contiguous ranges? (Excel 2003)
Well, my dummy test and the real document are set up differently.
Dummy test: Decision = D418 Dollars = E4:E18, G4:G18, I4:I18 Real document: Customer Funding Category: F:F Material Costs: I:I, M:M -- Ann Scharpf "T. Valko" wrote: In my test, I named this range DOLLARS. What are the individual range addresses that make up DOLLARS? I set up a column (named range "decision") with yes/no What is the range address that makes up DECISION? -- Biff Microsoft Excel MVP "Ann Scharpf" wrote in message ... I have a bunch of columns with dollar values. I want to add the sum of all the values for rows that meet a single condition. If possible, I'd like to create a named a range that includes all the non-contiguous dollar value columns and use a single SUMIF(). In my test, I named this range DOLLARS. The formula =SUM(DOLLARS) does work fine and adds up ALL the values. To test the SUMIF(), I set up a column (named range "decision") with yes/no values. I get a #VALUE error when I try the formula =SUMIF(decision,"=yes",dollars). I looked at some of the posts for array functions and I can't really tell if an array formula would fix this problem. Is this possible to do as a single function, or do I have to have something like this: =SUMIF(decision,"=yes",dollars1)+SUMIF(decision,"= yes",dollars2)... Thanks for your help. -- Ann Scharpf . |
#4
|
|||
|
|||
SUMIF() to add cells in non-contiguous ranges? (Excel 2003)
What I think Biff was asking about was what is the logic behind the
non-contiguous cell choices? As an quick example, this takes the sum of every 4th row that has a corresponding text of "Add" =SUMPRODUCT(--(NOT(MOD(ROW(A1:A20),4))),--(B1:B20="add"),(A1:A20)) Then you can get away from the use of named ranges. -- Best Regards, Luke M "Ann Scharpf" wrote in message news Well, my dummy test and the real document are set up differently. Dummy test: Decision = D418 Dollars = E4:E18, G4:G18, I4:I18 Real document: Customer Funding Category: F:F Material Costs: I:I, M:M -- Ann Scharpf "T. Valko" wrote: In my test, I named this range DOLLARS. What are the individual range addresses that make up DOLLARS? I set up a column (named range "decision") with yes/no What is the range address that makes up DECISION? -- Biff Microsoft Excel MVP "Ann Scharpf" wrote in message ... I have a bunch of columns with dollar values. I want to add the sum of all the values for rows that meet a single condition. If possible, I'd like to create a named a range that includes all the non-contiguous dollar value columns and use a single SUMIF(). In my test, I named this range DOLLARS. The formula =SUM(DOLLARS) does work fine and adds up ALL the values. To test the SUMIF(), I set up a column (named range "decision") with yes/no values. I get a #VALUE error when I try the formula =SUMIF(decision,"=yes",dollars). I looked at some of the posts for array functions and I can't really tell if an array formula would fix this problem. Is this possible to do as a single function, or do I have to have something like this: =SUMIF(decision,"=yes",dollars1)+SUMIF(decision,"= yes",dollars2)... Thanks for your help. -- Ann Scharpf . |
#5
|
|||
|
|||
SUMIF() to add cells in non-contiguous ranges? (Excel 2003)
Well, the non-contiguous range DOLLARS presents a problem.
Just because a range has a defined name doesn't mean you *have* to use that name! Here's how I would do it... =SUMPRODUCT(--(Decision="yes"),E4:E18+G4:G18+I4:I18) -- Biff Microsoft Excel MVP "Ann Scharpf" wrote in message news Well, my dummy test and the real document are set up differently. Dummy test: Decision = D418 Dollars = E4:E18, G4:G18, I4:I18 Real document: Customer Funding Category: F:F Material Costs: I:I, M:M -- Ann Scharpf "T. Valko" wrote: In my test, I named this range DOLLARS. What are the individual range addresses that make up DOLLARS? I set up a column (named range "decision") with yes/no What is the range address that makes up DECISION? -- Biff Microsoft Excel MVP "Ann Scharpf" wrote in message ... I have a bunch of columns with dollar values. I want to add the sum of all the values for rows that meet a single condition. If possible, I'd like to create a named a range that includes all the non-contiguous dollar value columns and use a single SUMIF(). In my test, I named this range DOLLARS. The formula =SUM(DOLLARS) does work fine and adds up ALL the values. To test the SUMIF(), I set up a column (named range "decision") with yes/no values. I get a #VALUE error when I try the formula =SUMIF(decision,"=yes",dollars). I looked at some of the posts for array functions and I can't really tell if an array formula would fix this problem. Is this possible to do as a single function, or do I have to have something like this: =SUMIF(decision,"=yes",dollars1)+SUMIF(decision,"= yes",dollars2)... Thanks for your help. -- Ann Scharpf . |
Thread Tools | |
Display Modes | |
|
|