Negative value to positive

[Mark74w1]

This formula,
=SUMIF('Cost Plus Invoice'!O8:O1500,"p",'(Cost Plus Invoice'!T8:U1500)
copys (to the cell it is written to) the sum of negative dollar amount
from another sheet.
I need to add to this formula, a formula that changes the negativ
dollar amount total to a positive.
I know that the abs function converts, but i'm not sure how t
incorporate it into this formula.
Please help.
Thanks, Mar

 

[Claus Busch]

Hi Mark,

Am Mon, 24 Jun 2013 19:53:10 +0100 schrieb Mark74w1:

This formula,
=SUMIF('Cost Plus Invoice'!O8:O1500,"p",'(Cost Plus Invoice'!T8:U1500)
copys (to the cell it is written to) the sum of negative dollar amounts
from another sheet.
I need to add to this formula, a formula that changes the negative
dollar amount total to a positive.
I know that the abs function converts, but i'm not sure how to
incorporate it into this formula.

try:
=ABS(SUMIF('Cost Plus Invoice'!O8:O1500,"p",'Cost Plus
Invoice'!T8:U1500))


Regards
Claus Busch

 

[joeu2004]

This formula,
=SUMIF('Cost Plus Invoice'!O8:O1500,"p",'(Cost Plus Invoice'!T8:U1500)
copys (to the cell it is written to) the sum of negative
dollar amounts from another sheet. I need to add to this
formula, a formula that changes the negative dollar amount
total to a positive. I know that the abs function converts,
but i'm not sure how to incorporate it into this formula.

Use the ABS function if the SUMIF result might be negative __sometimes__,
positive other times.

However, if the SUMIF result is __always__ negative ("the sum of negative
amounts"), you can simply negate it, to wit:

=-SUMIF('Cost Plus Invoice'!O8:O1500,"p",'(Cost Plus Invoice'!T8:U1500)

 

[Mark74w1]

This formula,
=SUMIF('Cost Plus Invoice'!O8:O1500,"p",'(Cost Plu Invoice'!T8:U1500)
copys (to the cell it is written to) the sum of negative
dollar amounts from another sheet. I need to add to this
formula, a formula that changes the negative dollar amount
total to a positive. I know that the abs function converts,
but i'm not sure how to incorporate it into this formula.-

Use the ABS function if the SUMIF result might be negativ
__sometimes__,
positive other times.

However, if the SUMIF result is __always__ negative ("the sum o
negative
amounts"), you can simply negate it, to wit:

=-SUMIF('Cost Plus Invoice'!O8:O1500,"p",'(Cost Plus Invoice'!T8:U1500)

Joe,
Thank you for the response on this problem.
For some reason, when I entered the corrected formula it opens m
documents looking for a file.
The reply by Mr. Busch worked, however I still want to find out why thi
formula didn't work so that I could use it as well.
Thanks, Mar

 

[joeu2004]

This formula,
=SUMIF('Cost Plus Invoice'!O8:O1500,"p",'(Cost Plus Invoice'!T8:U1500)

[....]
you can simply negate it, to wit:
=-SUMIF('Cost Plus Invoice'!O8:O1500,"p",'(Cost Plus Invoice'!T8:U1500)

Joe, Thank you for the response on this problem. For some
reason, when I entered the corrected formula it opens my
documents looking for a file. The reply by Mr. Busch worked,
however I still want to find out why this formula didn't work
so that I could use it as well.

I had copy-and-pasted your posted formula exactly as you had written it, not
realizing it has a typo: the left-parenthesis before the word "Cost" on the
right. Claus corrected your typo without drawing your attention to it.
Modifying Claus's formula:

=-SUMIF('Cost Plus Invoice'!O8:O1500,"p",'Cost Plus Invoice'!T8:U1500)

 

[CellShocked]

This formula,
=SUMIF('Cost Plus Invoice'!O8:O1500,"p",'(Cost Plus Invoice'!T8:U1500)
copys (to the cell it is written to) the sum of negative
dollar amounts from another sheet. I need to add to this
formula, a formula that changes the negative dollar amount
total to a positive. I know that the abs function converts,
but i'm not sure how to incorporate it into this formula.-

Use the ABS function if the SUMIF result might be negative
__sometimes__,
positive other times.

However, if the SUMIF result is __always__ negative ("the sum of
negative
amounts"), you can simply negate it, to wit:

=-SUMIF('Cost Plus Invoice'!O8:O1500,"p",'(Cost Plus Invoice'!T8:U1500)

Joe,
Thank you for the response on this problem.
For some reason, when I entered the corrected formula it opens my
documents looking for a file.
The reply by Mr. Busch worked, however I still want to find out why this
formula didn't work so that I could use it as well.
Thanks, Mark

Cell formatting negated it being seen as a formula?

 

[Mark74w1]

'joeu2004 Wrote:-

This formula,
=SUMIF('Cost Plus Invoice'!O8:O1500,"p",'(Cost Plu

Invoice'!T8:U1500)---
[....]--

you can simply negate it, to wit:
=-SUMIF('Cost Plus Invoice'!O8:O1500,"p",'(Cost Plu

Invoice'!T8:U1500)-

Joe, Thank you for the response on this problem. For some
reason, when I entered the corrected formula it opens my
documents looking for a file. The reply by Mr. Busch worked,
however I still want to find out why this formula didn't work
so that I could use it as well.-

I had copy-and-pasted your posted formula exactly as you had written it
not
realizing it has a typo: the left-parenthesis before the word "Cost" o
the
right. Claus corrected your typo without drawing your attention to it

Modifying Claus's formula:

=-SUMIF('Cost Plus Invoice'!O8:O1500,"p",'Cost Plus Invoice'!T8:U1500)

Joe,
Thank you again for the help. I didn't see that either.
Can I ask you one more question? I've tried to make this work with n
success. =SUMIF('Cost Plus Invoice'!O8:O1500,"p",'Cost Plu
Invoice'!T8:U1500)+('payroll'!O8:O1500,"t",'payroll'!T8:U1500).
The purpose of this formula is to sometimes add the two, or to have on
entered if the other is blank.
Also can you, if payroll is "t" and cost plus is "p" then total th
two.
I hope I'm not taking advantage of your expertise, but these tw
solutions could greatly simplify my 9 page spread sheet.
Thanks Mar

 

[joeu2004]

I've tried to make this work with no success.
=SUMIF('Cost Plus Invoice'!O8:O1500,"p",'Cost Plus
Invoice'!T8:U1500)+('payroll'!O8:O1500,"t",'payroll'!T8:U1500).
The purpose of this formula is to sometimes add the two,
or to have one entered if the other is blank.
Also can you, if payroll is "t" and cost plus is "p" then
total the two.

I'm not sure I understand the requirement(s?).

First, I assume that 'cost plus invoice'!T8:U1500 is still negative, and you
want the sum to be positive. But I assume that payroll!T8:U1500 is
positive.

If you want to sum 'cost plus invoice'!T8:U1500 when O8:O1500 is "p" and sum
payroll!T8:U1500 when O8:O1500 is "t", then add the two sums, that would be:

=SUMIF(payroll!O8:O1500,"t",payroll!T8:U1500)
- SUMIF('Cost Plus Invoice'!O8:O1500,"p",'Cost Plus Invoice'!T8:U1500)

If you want to sum the two only when both conditions are met in
corresponding rows, that would be:

=SUMPRODUCT(('Cost Plus Invoice'!O8:O1500="p")*(payroll!O8:O1500="t"),
payroll!T8:U1500 - 'Cost Plus Invoice'!T8:U1500)

Please correct my interpretation of the requirements, if it is wrong.
Perhaps a concrete example that shows when to add and not add the two would
clarify any misunderstanding.

 

[Mark74w1]

Hi Mark

Am Mon, 24 Jun 2013 19:53:10 +0100 schrieb Mark74w1


try
=ABS(SUMIF('Cost Plus Invoice'!O8:O1500,"p",'Cost Plu
Invoice'!T8:U1500)


Regard
Claus Busc

Mr. Busc
Can you help me again
=SUMIF('Cost Plus Invoice'!O$8:O$1510,"t,m,r",'Cost Plu
Invoice'!T$8:U$1510
Does not work: this means if troy or Mark or Robert pays the bill
Also
=SUMIF('Cost Plus Invoice'!O$8:O$1510,"t" when 'Cost plu
invoice!m$8:m1500,"p",'Cost Plus Invoice'!T$8:U$1510
This means if o is "t" and "m" is p, then sum

These two formulas would simplify my 9 page spread sheet
Thanks Mar

 

[Claus Busch]

Hi Mark,

Am Thu, 27 Jun 2013 18:04:45 +0100 schrieb Mark74w1:

=SUMIF('Cost Plus Invoice'!O$8:O$1510,"t,m,r",'Cost Plus
Invoice'!T$8:U$1510)
try:
=SUMPRODUCT(((O8:O1500="t")+(O8:O1500="m")+(O8:O1500="r"))*T8:U1500)

=SUMIF('Cost Plus Invoice'!O$8:O$1510,"t" when 'Cost plus
invoice!m$8:m1500,"p",'Cost Plus Invoice'!T$8:U$1510)
This means if o is "t" and "m" is p, then sum.

try:
=SUMPRODUCT(--(M8:M1500="p"),--(O8:O1500="t"),T8:U1500)


Regards
Claus B

 

[Claus Busch]

Hi Mark,

Am Thu, 27 Jun 2013 20:04:37 +0200 schrieb Claus Busch:

=SUMPRODUCT(--(M8:M1500="p"),--(O8:O1500="t"),T8:U1500)

there is a typo above. Try:
=SUMPRODUCT((M8:M1500="p")*(O8:O1500="t")*(T8:T1500+U8:U1500))


Regards
Claus Busch

 

[Claus Busch]

Hi Mark,

Am Thu, 27 Jun 2013 20:09:13 +0200 schrieb Claus Busch:

=SUMPRODUCT((M8:M1500="p")*(O8:O1500="t")*(T8:T1500+U8:U1500))

sorry, I forgot the sheet name
=SUMPRODUCT((('Cost Plus Invoice'!O8:O1500="t")+('Cost Plus Invoice'!O8:O1500="m")+('Cost Plus Invoice'!O8:O1500="r"))*'Cost Plus Invoice'!T8:U1500)
=SUMPRODUCT(('Cost Plus Invoice'!M8:M1500="p")*('Cost Plus Invoice'!O8:O1500="t")*('Cost Plus Invoice'!T8:T1500+'Cost Plus Invoice'!U8:U1500))


Regards
Claus B.

 

[Mark74w1]

I've tried to make this work with no success.
=SUMIF('Cost Plus Invoice'!O8:O1500,"p",'Cost Plus
Invoice'!T8:U1500)+('payroll'!O8:O1500,"t",'payroll'!T8:U1500).
The purpose of this formula is to sometimes add the two,
or to have one entered if the other is blank.
Also can you, if payroll is "t" and cost plus is "p" then
total the two.-

I'm not sure I understand the requirement(s?).

First, I assume that 'cost plus invoice'!T8:U1500 is still negative, an
you
want the sum to be positive. But I assume that payroll!T8:U1500 is
positive.

If you want to sum 'cost plus invoice'!T8:U1500 when O8:O1500 is "p" an
sum
payroll!T8:U1500 when O8:O1500 is "t", then add the two sums, that woul
be:

=SUMIF(payroll!O8:O1500,"t",payroll!T8:U1500)
- SUMIF('Cost Plus Invoice'!O8:O1500,"p",'Cost Plus Invoice'!T8:U1500)

If you want to sum the two only when both conditions are met in
corresponding rows, that would be:

=SUMPRODUCT(('Cost Plus Invoice'!O8:O1500="p")*(payroll!O8:O1500="t"),
payroll!T8:U1500 - 'Cost Plus Invoice'!T8:U1500)

Please correct my interpretation of the requirements, if it is wrong.
Perhaps a concrete example that shows when to add and not add the tw
would
clarify any misunderstanding.

Hi Joe,
I'll explain.

=SUMIF('Cost Plus Invoice'!O8:O1500,"p",'Cost Plus Invoice'!T8:U1500
And, or ('payroll'!O8:O1500,"t",'payroll'!T8:U1500).

Joe, My spread sheet has 9 sheets. Home, Takeoff, Phase totals, Cos
entry, Cost plus invoice w/ phase totals, Payroll, Employee info an
Profit and loss all hyperlinked to each other. The profit and loss shee
has to share totals from two different sources in the same cell
sometimes I'll use the totals from the fixed contract side (takeoff
cost entry, phases and profit & Loss) and sometimes I use the Cost plu
side (Cost plus invoice, phases & Profit and loss).
The purpose of this formula is to sometimes add the two totals to
single cell or to just have one entered if the other sheet has no "p" o
no "t" Etc.

The other condition would be to only get the total if (payroll or an
other sheet I choose has the criteria that a cell has (for example)"t
in one cell and a "p" in another cell (That both cells must have
letter in them or nothing will totaled in the cell with the =sumi
formula.

It's so hard to explain on print when a verbal would do it in a secant.
Maybe I should find a way for you to play around with the entire wor
book.
Thanks, Mar

 

[joeu2004]

=SUMIF(payroll!O8:O1500,"t",payroll!T8:U1500)
- SUMIF('Cost Plus Invoice'!O8:O1500,"p",'Cost Plus Invoice'!T8:U1500) [....]
=SUMPRODUCT(('Cost Plus Invoice'!O8:O1500="p")*(payroll!O8:O1500="t"),
payroll!T8:U1500 - 'Cost Plus Invoice'!T8:U1500)

[....]
The purpose of this formula is to sometimes add the two totals to a
single cell or to just have one entered if the other sheet has no "p"
or no "t" Etc.

The other condition would be to only get the total if (payroll or any
other sheet I choose has the criteria that a cell has (for example)"t"
in one cell and a "p" in another cell (That both cells must have a
letter in them or nothing will totaled in the cell with the =sumif
formula.

And I believe that is exactly what those two formulas do.

If you have examples where one or the other formula does not produce
expected results, please post the circumstances (values) of those examples.

However, perhaps the issue is the idea of "add". As I noted previously, I
assume the same conditions and requirements as your previous postings in
this thread, to wit: the 'cost plus invoice'!T8:U1500 values are negative,
and you want to add their absolute value. This can be accomplished
using -SUMIF(...). So in effect, the SUMIF formula above could be written
=SUMIF(...) + -SUMIF(...). But that can be simplified to =SUMIF(...) -
SUMIF(...). Similarly for the SUMPRODUCT expressions.

If you are applying the formulas above to circumstances involving only
positive values, change the minus ("-") to plus ("+").