This won't work:

=SUM(T8:U27)-(IF(ISBLANK('Cost Plus Invoice'!O8:O1500)," ",('Cost Plus Invoice'!T8:U1500)))

This formula means,
The sum of T8:T27 (less) the sum of 'Cost Plus Invoice'!T8:U1500 only when cells 'Cost Plus Invoice'!O8:O1500 are empty.

In other words:
If the cell in row "o8" of sheet (Cost plus invoice) is empty, the $ amount in
row T8 will be subtracted from the amount in the cell that has the formula =sum(T8:U27)

Again, I'm sure it's a simple fix but I haven't been able to figure it out.
I tried a round about in my negative into positive # help request. but that seems to be the wrong approach.

I truly appreciate all the help I've been receiving in my quest to finish this rather elaborate spreadsheet.

Thanks, Mark

Hi Mark,

Am Fri, 28 Jun 2013 17:45:52 +0100 schrieb Mark74w1:

=SUM(T8:U27)-(IF(ISBLANK('Cost Plus Invoice'!O8:O1500)," ",('Cost Plus
Invoice'!T8:U1500)))

try:
=SUM(T8:U27)-SUMPRODUCT(--('Cost Plus Invoice'!O8:O1500=""),('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

Mr. Clause
I copied and pasted the formula and got a #REF! Error.
I checked that the formula spelled out correct places and it seems correct.

Mark

Hi Mark,

Am Fri, 28 Jun 2013 23:45:32 +0100 schrieb Mark74w1:

I copied and pasted the formula and got a #REF! Error.
I checked that the formula spelled out correct places and it seems
correct.

the formula is tested and it works well. Did you pay attention about the
line break?


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

Claus,
I entered:

=SUM(T8:U27)-SUMPRODUCT(--('Cost Plus Invoice'!O8:O1320=""),('Cost Plus Invoice'!T8:T1320+'Cost Plus Invoice'!U8:U1320))

1. With a change of 1320 instead of 1500 thinking that (going past the bottom of the sheet where I hid the remaining rows)was the problem.
that didn't work.

2. I tried:
=SUM(T8:U27)-SUMPRODUCT(--('Cost Plus Invoice'!O8:O1320=""),('Cost Plus Invoice'!T8:U1320)) That didn't work.

3. I thought the 6 merged cells weren't enough space (even reducing the font size from 9 to 8 and tried (what I guessed was the proper line brake ???) and that didn't work.

4. I even merged the entire bottom row so the formula did not have to go to a second line, and no success.

5. I also made the column "o" that has the cells (that contain letters or are blank) a currency so that they match the currency columns of my totals. No luck.

What am I doing wrong.

Did I explain correctly that the column "o" that contains rows of letters or blanks, needs to add all the $ amounts in the corresponding rows of column t8:u1320 (that are blank only) to the cell that contains the formula that I'm having trouble with.

I'm Certain that you deciphered that from my crude description of the scope of the formula.

Thanks, Mark

Hi Mark,

Am Sat, 29 Jun 2013 15:12:29 +0100 schrieb Mark74w1:

1. With a change of 1320 instead of 1500 thinking that (going past the
bottom of the sheet where I hid the remaining rows)was the problem.
that didn't work.

I don't know where your error is. Please look he
https://skydrive.live.com/#cid=9378A...121822A3%21326
for the workbook "Sumproduct"


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

On Sat, 29 Jun 2013 15:12:29 +0100, Mark74w1 wrote:

3. I thought the 6 merged cells weren't enough space (even reducing the
font size from 9 to 8 and tried (what I guessed was the proper line
brake ???) and that didn't work.

The entire formula should be in one single cell; not in a bunch of merged cells; and you should eliminate the line break.

Claus.
I think I think I see the problem.
When I looked at the file that you created, it works in all single cells.
In my spread sheet the cells are as follows:

Row 8 has column T & U merged on both the target sheet and the cost plus invoice sheet.

=SUM(T8:U27)[T8 & U8, T9 & U9 etc. are merged]-SUMPRODUCT(--('Cost Plus Invoice'!O8:O1329=""),('Cost Plus Invoice'!T8[& U8, T9 & U9 are merged]:T1329+'Cost Plus Invoice'!U8:U1329)) (

There is only one sum in these merged cells [T8 & U8, Etc.] and the same applies for the sums on sheet [Cost plus Invoice T8 & U8 Etc.

Do the cells have to be un merged to work?

Thanks Mark

Clause, I forgot to tell you that the target cells for the formula are also merged (two across and two down)
Thanks,Mark