Quote:
Originally Posted by joeu2004[_2_]
"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.
|
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