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

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

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

"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

"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)

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

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?

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.