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.

Calculating total value based on another cell's state
 

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.

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

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.

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.

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

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