Calculating total value based on another cell's state

[Guest]

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.

 

[Guest]

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.

 

[smw226 via OfficeKB.com]

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

 

[Guest]

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.

 

[Guest]

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.

 

[smw226 via OfficeKB.com]

Hi Nero,

The formula is called SUMIF so:

=SUMIF(N3:N13,"=x",F3:F13)

Thanks,

Simon

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]

 

[Guest]

This one worked perfectly Thanks!!

Hi Nero,

The formula is called SUMIF so:

=SUMIF(N3:N13,"=x",F3:F13)

Thanks,

Simon

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.