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.
Please help.
Thanks, Mark

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

"Mark74w1" wrote:
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)

Mr. Busch,
Thank you for you help with this formula. Obviously I'm not
quite there yet with understanding the formatting of formulas.
I would sure like to know if there was a simple rule of thumb for this.
Thanks, Mark

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

"Mark74w1" wrote:
'joeu2004 Wrote:
"Mark74w1" wrote:
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)

On Tue, 25 Jun 2013 15:10:20 +0100, Mark74w1
wrote:


'joeu2004[_2_ Wrote:
;1612463']"Mark74w1" wrote:-
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?

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 no success. =SUMIF('Cost Plus Invoice'!O8:O1500,"p",'Cost Plus Invoice'!T8:U1500)+('payroll'!O8:O1500,"t",'payrol l'!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 hope I'm not taking advantage of your expertise, but these two solutions could greatly simplify my 9 page spread sheet.
Thanks Mark

CellShocked,
Thanks for interest in helping me.
I'm starting to get the hang of this but there are so many tricks that I need to learn.
Thanks again, Mark

"Mark74w1" wrote:
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",'payrol l'!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.

Mr. Busch
Can you help me again?
=SUMIF('Cost Plus Invoice'!O$8:O$1510,"t,m,r",'Cost Plus 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 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.

These two formulas would simplify my 9 page spread sheet.
Thanks Mark

Can you help me again?
=SUMIF('Cost Plus Invoice'!O$8:O$1510,"t,m,r",'Cost Plus 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 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.

These two formulas would simplify my 9 page spread sheet.
Thanks Mark

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:O15 00="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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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:T150 0+U8:U1500))


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi Mark,

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

=SUMPRODUCT((M8:M1500="p")*(O8:O1500="t")*(T8:T150 0+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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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, Cost entry, Cost plus invoice w/ phase totals, Payroll, Employee info and Profit and loss all hyperlinked to each other. The profit and loss sheet 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 plus side (Cost plus invoice, phases & Profit and loss).
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.

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 work book.
Thanks, Mark

"Mark74w1" wroteL:
'joeu2004 Wrote:
=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 ("+").