if cell is blank

[Mark74w1]

This won't work:

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

This formula means,
The sum of T8:T27 (less) the sum of 'Cost Plus Invoice'!T8:U1500 onl
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 th
formula =sum(T8:U27)

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

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

Thanks, Mar

 

[Claus Busch]

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.

 

[Mark74w1]

Hi Mark,

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

try:
=SUM(T8:U27)-SUMPRODUCT(--('Cost Plus Invoice'!O8:O1500=""),('Cost Plus
Invoice'!T8:T1500+'Cost Plus Invoice'!U8:U1500))


Regards
Claus B.

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

Mar

 

[Claus Busch]

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.

 

[Mark74w1]

Hi Mark

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


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


Regard
Claus B

Claus
I entered

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

1. With a change of 1320 instead of 1500 thinking that (going past th
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 Plu
Invoice'!T8:U1320)) That didn't work

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

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

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

What am I doing wrong

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

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

Thanks, Mar

 

[Claus Busch]

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 here:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for the workbook "Sumproduct"


Regards
Claus B.

 

[Ron Rosenfeld]

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.

 

[Mark74w1]

Hi Mark,

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

I don't know where your error is. Please look he
http://tinyurl.com/3s4zwaz
for the workbook "Sumproduct"


Regards
Claus B.

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

 

[Mark74w1]

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