Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating total value based on another cell's state
Hi group,
I have an XL that tracks all of the proposals that I send out since my accounting software generates an invoice number. As is normal, not all proposals are accepted so I don't want to input them until they are approved which is why I want to be able to track this separately Right now I input the project information and cost (1 per column) and it calculates the tax fields for me on the fly. At the end of the document I have a total that adds all these values and calculates the total of all the values to give me my anticipated totals. Now I've added a column called "paid" that can be filled in with any variable and is available for every row in the table. I'm using the letter "x". What I want to do is have another total calculation that only includes "current", that's to say, only items that I've marked as paid. I've gotten conditional formatting to work so I see completed but unpaid invoices in red. Columns F, J, K, L contain dollar values for each row Cells F16, J16, K16, L16 total all the values available in columns F,J,K,L What I want is for cells F18, J16, K16, L16 to include the values for each row in which column N (paid) is checked off. How can I do this? Thanks in advance. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating total value based on another cell's state
Hi Nero,
If you want to sum the values if your row is marked "paid" you can use the below. For this example I will assume cells A2:A20 contain your values and cells B2: B20 contain your "paid" marker: =sumif(B2:B20,"=paid",A2:A20) Thanks, Simon neromadrid wrote: Hi group, I have an XL that tracks all of the proposals that I send out since my accounting software generates an invoice number. As is normal, not all proposals are accepted so I don't want to input them until they are approved which is why I want to be able to track this separately Right now I input the project information and cost (1 per column) and it calculates the tax fields for me on the fly. At the end of the document I have a total that adds all these values and calculates the total of all the values to give me my anticipated totals. Now I've added a column called "paid" that can be filled in with any variable and is available for every row in the table. I'm using the letter "x". What I want to do is have another total calculation that only includes "current", that's to say, only items that I've marked as paid. I've gotten conditional formatting to work so I see completed but unpaid invoices in red. Columns F, J, K, L contain dollar values for each row Cells F16, J16, K16, L16 total all the values available in columns F,J,K,L What I want is for cells F18, J16, K16, L16 to include the values for each row in which column N (paid) is checked off. How can I do this? Thanks in advance. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200610/1 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating total value based on another cell's state
Hello again,
Thanks for your example...I translated the formula you had into this for my table: =SUM(IF(N3:N13="x",F3:F13,0)) I'm getting #value! as an error... When I go into Evaluate Formula I see this SUM(IF(#VALUE!="x",F3:F13,0)) I've rechecked and can't isolate the issue since there are dollar values in that field. Does it matter what type of formatting I've given to the N column i.e. number, currency, special? Thanks again. "galimi" wrote: I've put an example spreadsheet @ http://www.galimi.com/Examples/paid.xls The formula to calculate paid versus unpaid is a simple formulaic array. -- http://HelpExcel.com 516-984-0252 "neromadrid" wrote: Hi group, I have an XL that tracks all of the proposals that I send out since my accounting software generates an invoice number. As is normal, not all proposals are accepted so I don't want to input them until they are approved which is why I want to be able to track this separately Right now I input the project information and cost (1 per column) and it calculates the tax fields for me on the fly. At the end of the document I have a total that adds all these values and calculates the total of all the values to give me my anticipated totals. Now I've added a column called "paid" that can be filled in with any variable and is available for every row in the table. I'm using the letter "x". What I want to do is have another total calculation that only includes "current", that's to say, only items that I've marked as paid. I've gotten conditional formatting to work so I see completed but unpaid invoices in red. Columns F, J, K, L contain dollar values for each row Cells F16, J16, K16, L16 total all the values available in columns F,J,K,L What I want is for cells F18, J16, K16, L16 to include the values for each row in which column N (paid) is checked off. How can I do this? Thanks in advance. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating total value based on another cell's state
Hello again,
Thanks for your example...I translated the formula you had into this for my table: =SUM(IF(N3:N13="x",F3:F13,0)) I'm getting #value! as an error... When I go into Evaluate Formula I see this SUM(IF(#VALUE!="x",F3:F13,0)) I've rechecked and can't isolate the issue since there are dollar values in that field. Does it matter what type of formatting I've given to the N column i.e. number, currency, special? Thanks again. "galimi" wrote: I've put an example spreadsheet @ http://www.galimi.com/Examples/paid.xls The formula to calculate paid versus unpaid is a simple formulaic array. -- http://HelpExcel.com 516-984-0252 "neromadrid" wrote: Hi group, I have an XL that tracks all of the proposals that I send out since my accounting software generates an invoice number. As is normal, not all proposals are accepted so I don't want to input them until they are approved which is why I want to be able to track this separately Right now I input the project information and cost (1 per column) and it calculates the tax fields for me on the fly. At the end of the document I have a total that adds all these values and calculates the total of all the values to give me my anticipated totals. Now I've added a column called "paid" that can be filled in with any variable and is available for every row in the table. I'm using the letter "x". What I want to do is have another total calculation that only includes "current", that's to say, only items that I've marked as paid. I've gotten conditional formatting to work so I see completed but unpaid invoices in red. Columns F, J, K, L contain dollar values for each row Cells F16, J16, K16, L16 total all the values available in columns F,J,K,L What I want is for cells F18, J16, K16, L16 to include the values for each row in which column N (paid) is checked off. How can I do this? Thanks in advance. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating total value based on another cell's state
Hi Nero,
The formula is called SUMIF so: =SUMIF(N3:N13,"=x",F3:F13) Thanks, Simon neroamdrid wrote: Hello again, Thanks for your example...I translated the formula you had into this for my table: =SUM(IF(N3:N13="x",F3:F13,0)) I'm getting #value! as an error... When I go into Evaluate Formula I see this SUM(IF(#VALUE!="x",F3:F13,0)) I've rechecked and can't isolate the issue since there are dollar values in that field. Does it matter what type of formatting I've given to the N column i.e. number, currency, special? Thanks again. I've put an example spreadsheet @ http://www.galimi.com/Examples/paid.xls The formula to calculate paid versus unpaid is a simple formulaic array. [quoted text clipped - 26 lines] Thanks in advance. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200610/1 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating total value based on another cell's state
This one worked perfectly Thanks!!
"smw226 via OfficeKB.com" wrote: Hi Nero, The formula is called SUMIF so: =SUMIF(N3:N13,"=x",F3:F13) Thanks, Simon neroamdrid wrote: Hello again, Thanks for your example...I translated the formula you had into this for my table: =SUM(IF(N3:N13="x",F3:F13,0)) I'm getting #value! as an error... When I go into Evaluate Formula I see this SUM(IF(#VALUE!="x",F3:F13,0)) I've rechecked and can't isolate the issue since there are dollar values in that field. Does it matter what type of formatting I've given to the N column i.e. number, currency, special? Thanks again. I've put an example spreadsheet @ http://www.galimi.com/Examples/paid.xls The formula to calculate paid versus unpaid is a simple formulaic array. [quoted text clipped - 26 lines] Thanks in advance. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200610/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mileage Claim Formula | New Users to Excel | |||
Pivot table for reporting sales performance | Excel Discussion (Misc queries) | |||
Macro to highlight cells based on content | Excel Worksheet Functions | |||
moving cells based certain value | New Users to Excel | |||
CALCULATING CELLS AS A TOTAL | Excel Discussion (Misc queries) |