Excel 2003

I want to have the total at the bottom of column A include only those values
in column A that do NOT have a date in the corresponding cell in column B.

This will be a dynamic spreadsheet with column A representing sums outlaid
for purchases and column B the date upon which the item was sold. The
spreadsheet is somewhat more complex than merely column A & B and the dynamic
total at the bottom of Column A is used in another formula.

Try this:

=SUMIF(B1:B10,"",A1:A10)

--
Biff
Microsoft Excel MVP


"Beckett" wrote in message
...
Excel 2003

I want to have the total at the bottom of column A include only those
values
in column A that do NOT have a date in the corresponding cell in column B.

This will be a dynamic spreadsheet with column A representing sums outlaid
for purchases and column B the date upon which the item was sold. The
spreadsheet is somewhat more complex than merely column A & B and the
dynamic
total at the bottom of Column A is used in another formula.

Thanks for that. Unfortunately it results in a zero sum.

Total Date Sold
9,619.95 21/04/2008
12,279.95 12/03/2008
7,769.95 12/02/2008
9,819.95 28/03/2008
12,439.95 20/03/2008
7,507.95
20,029.95

0.00

Whereas I was hoping for 27,537.90

Any other suggestion? Thanks

Beckett
"T. Valko" wrote:

Try this:

=SUMIF(B1:B10,"",A1:A10)

--
Biff
Microsoft Excel MVP


"Beckett" wrote in message
...
Excel 2003

I want to have the total at the bottom of column A include only those
values
in column A that do NOT have a date in the corresponding cell in column B.

This will be a dynamic spreadsheet with column A representing sums outlaid
for purchases and column B the date upon which the item was sold. The
spreadsheet is somewhat more complex than merely column A & B and the
dynamic
total at the bottom of Column A is used in another formula.

See this screencap:

http://img205.imageshack.us/img205/9712/sumifrx5.jpg

If you're getting a result of 0 then there's a problem with your data. The
dollar amounts might be TEXT.

Try one of these:

Select the range of dollar amounts.
Goto DataText to Columns
Click Finish

Sometimes that'll convert TEXT numbers to numeric numbers.

If that doesn't work try this:

Select the range of dollar amounts
Goto FormatCellsNumber tabGENERALOK
Select an empty cell that has not been preformatted
Copy that cell: Goto EditCopy
Select the range of dollar amounts
Goto EditPaste SpecialAddOK

--
Biff
Microsoft Excel MVP


"Beckett" wrote in message
...

Thanks for that. Unfortunately it results in a zero sum.

Total Date Sold
9,619.95 21/04/2008
12,279.95 12/03/2008
7,769.95 12/02/2008
9,819.95 28/03/2008
12,439.95 20/03/2008
7,507.95
20,029.95

0.00

Whereas I was hoping for 27,537.90

Any other suggestion? Thanks

Beckett
"T. Valko" wrote:

Try this:

=SUMIF(B1:B10,"",A1:A10)

--
Biff
Microsoft Excel MVP


"Beckett" wrote in message
...
Excel 2003

I want to have the total at the bottom of column A include only those
values
in column A that do NOT have a date in the corresponding cell in column
B.

This will be a dynamic spreadsheet with column A representing sums
outlaid
for purchases and column B the date upon which the item was sold. The
spreadsheet is somewhat more complex than merely column A & B and the
dynamic
total at the bottom of Column A is used in another formula.

Biff,

I am probably right on the edge of becoming a right, royal pain in the rear
end however . . .

I have tried everything you suggested, but still no change to my zero result.

Wondering if the fact that the numbers in column 'A' are all derived from
formulae, may be having an influence, I created a new work book and, ensuring
that Column A was formatted for numbergeneral, I entered some random numbers
and put some dates in Column B. I then applied your SUMIF formula and,
again, the result is zero. I have checked, double checked and double-double
checked to make sure I don't have the formula wrong.

It is beyond me because, following your hyper-link, I could see that it
works for you.

Beckett
"T. Valko" wrote:

See this screencap:

http://img205.imageshack.us/img205/9712/sumifrx5.jpg

If you're getting a result of 0 then there's a problem with your data. The
dollar amounts might be TEXT.

Try one of these:

Select the range of dollar amounts.
Goto DataText to Columns
Click Finish

Sometimes that'll convert TEXT numbers to numeric numbers.

If that doesn't work try this:

Select the range of dollar amounts
Goto FormatCellsNumber tabGENERALOK
Select an empty cell that has not been preformatted
Copy that cell: Goto EditCopy
Select the range of dollar amounts
Goto EditPaste SpecialAddOK

--
Biff
Microsoft Excel MVP


"Beckett" wrote in message
...

Thanks for that. Unfortunately it results in a zero sum.

Total Date Sold
9,619.95 21/04/2008
12,279.95 12/03/2008
7,769.95 12/02/2008
9,819.95 28/03/2008
12,439.95 20/03/2008
7,507.95
20,029.95

0.00

Whereas I was hoping for 27,537.90

Any other suggestion? Thanks

Beckett
"T. Valko" wrote:

Try this:

=SUMIF(B1:B10,"",A1:A10)

--
Biff
Microsoft Excel MVP


"Beckett" wrote in message
...
Excel 2003

I want to have the total at the bottom of column A include only those
values
in column A that do NOT have a date in the corresponding cell in column
B.

This will be a dynamic spreadsheet with column A representing sums
outlaid
for purchases and column B the date upon which the item was sold. The
spreadsheet is somewhat more complex than merely column A & B and the
dynamic
total at the bottom of Column A is used in another formula.

Hmmm...

Well, I'm getting reading to call it a day. It's almost 3:00 AM where I'm
at.

Try this:

=SUMPRODUCT(--(LEN(TRIM(B2:B6))=0),A2:A8+0)

--
Biff
Microsoft Excel MVP


"Beckett" wrote in message
...
Biff,

I am probably right on the edge of becoming a right, royal pain in the
rear
end however . . .

I have tried everything you suggested, but still no change to my zero
result.

Wondering if the fact that the numbers in column 'A' are all derived from
formulae, may be having an influence, I created a new work book and,
ensuring
that Column A was formatted for numbergeneral, I entered some random
numbers
and put some dates in Column B. I then applied your SUMIF formula and,
again, the result is zero. I have checked, double checked and
double-double
checked to make sure I don't have the formula wrong.

It is beyond me because, following your hyper-link, I could see that it
works for you.

Beckett
"T. Valko" wrote:

See this screencap:

http://img205.imageshack.us/img205/9712/sumifrx5.jpg

If you're getting a result of 0 then there's a problem with your data.
The
dollar amounts might be TEXT.

Try one of these:

Select the range of dollar amounts.
Goto DataText to Columns
Click Finish

Sometimes that'll convert TEXT numbers to numeric numbers.

If that doesn't work try this:

Select the range of dollar amounts
Goto FormatCellsNumber tabGENERALOK
Select an empty cell that has not been preformatted
Copy that cell: Goto EditCopy
Select the range of dollar amounts
Goto EditPaste SpecialAddOK

--
Biff
Microsoft Excel MVP


"Beckett" wrote in message
...

Thanks for that. Unfortunately it results in a zero sum.

Total Date Sold
9,619.95 21/04/2008
12,279.95 12/03/2008
7,769.95 12/02/2008
9,819.95 28/03/2008
12,439.95 20/03/2008
7,507.95
20,029.95

0.00

Whereas I was hoping for 27,537.90

Any other suggestion? Thanks

Beckett
"T. Valko" wrote:

Try this:

=SUMIF(B1:B10,"",A1:A10)

--
Biff
Microsoft Excel MVP


"Beckett" wrote in message
...
Excel 2003

I want to have the total at the bottom of column A include only
those
values
in column A that do NOT have a date in the corresponding cell in
column
B.

This will be a dynamic spreadsheet with column A representing sums
outlaid
for purchases and column B the date upon which the item was sold.
The
spreadsheet is somewhat more complex than merely column A & B and
the
dynamic
total at the bottom of Column A is used in another formula.

You're the man T Valko!

We have Lift-off!

I thought I was good. working at home on a public holiday (Labor Day
Queensland Australia) but 3.00am is way beyond the call of duty.

Thanks very much for your help and patience..

Beckett


"T. Valko" wrote:

Hmmm...

Well, I'm getting reading to call it a day. It's almost 3:00 AM where I'm
at.

Try this:

=SUMPRODUCT(--(LEN(TRIM(B2:B6))=0),A2:A8+0)

--
Biff
Microsoft Excel MVP


"Beckett" wrote in message
...
Biff,

I am probably right on the edge of becoming a right, royal pain in the
rear
end however . . .

I have tried everything you suggested, but still no change to my zero
result.

Wondering if the fact that the numbers in column 'A' are all derived from
formulae, may be having an influence, I created a new work book and,
ensuring
that Column A was formatted for numbergeneral, I entered some random
numbers
and put some dates in Column B. I then applied your SUMIF formula and,
again, the result is zero. I have checked, double checked and
double-double
checked to make sure I don't have the formula wrong.

It is beyond me because, following your hyper-link, I could see that it
works for you.

Beckett
"T. Valko" wrote:

See this screencap:

http://img205.imageshack.us/img205/9712/sumifrx5.jpg

If you're getting a result of 0 then there's a problem with your data.
The
dollar amounts might be TEXT.

Try one of these:

Select the range of dollar amounts.
Goto DataText to Columns
Click Finish

Sometimes that'll convert TEXT numbers to numeric numbers.

If that doesn't work try this:

Select the range of dollar amounts
Goto FormatCellsNumber tabGENERALOK
Select an empty cell that has not been preformatted
Copy that cell: Goto EditCopy
Select the range of dollar amounts
Goto EditPaste SpecialAddOK

--
Biff
Microsoft Excel MVP


"Beckett" wrote in message
...

Thanks for that. Unfortunately it results in a zero sum.

Total Date Sold
9,619.95 21/04/2008
12,279.95 12/03/2008
7,769.95 12/02/2008
9,819.95 28/03/2008
12,439.95 20/03/2008
7,507.95
20,029.95

0.00

Whereas I was hoping for 27,537.90

Any other suggestion? Thanks

Beckett
"T. Valko" wrote:

Try this:

=SUMIF(B1:B10,"",A1:A10)

--
Biff
Microsoft Excel MVP


"Beckett" wrote in message
...
Excel 2003

I want to have the total at the bottom of column A include only
those
values
in column A that do NOT have a date in the corresponding cell in
column
B.

This will be a dynamic spreadsheet with column A representing sums
outlaid
for purchases and column B the date upon which the item was sold.
The
spreadsheet is somewhat more complex than merely column A & B and
the
dynamic
total at the bottom of Column A is used in another formula.

Ok, we got something that worked! Progress!

However, that's a "last resort" formula. If that formula works then that
indicates there is a problem with your data.

There might be space characters in the in the cells that "appear" empty.

The formula also *forces* the dollar amounts to be calculated as numeric
values *even though they may be TEXT values*.

--
Biff
Microsoft Excel MVP


"Beckett" wrote in message
...

You're the man T Valko!

We have Lift-off!

I thought I was good. working at home on a public holiday (Labor Day
Queensland Australia) but 3.00am is way beyond the call of duty.

Thanks very much for your help and patience..

Beckett


"T. Valko" wrote:

Hmmm...

Well, I'm getting reading to call it a day. It's almost 3:00 AM where I'm
at.

Try this:

=SUMPRODUCT(--(LEN(TRIM(B2:B6))=0),A2:A8+0)

--
Biff
Microsoft Excel MVP


"Beckett" wrote in message
...
Biff,

I am probably right on the edge of becoming a right, royal pain in the
rear
end however . . .

I have tried everything you suggested, but still no change to my zero
result.

Wondering if the fact that the numbers in column 'A' are all derived
from
formulae, may be having an influence, I created a new work book and,
ensuring
that Column A was formatted for numbergeneral, I entered some random
numbers
and put some dates in Column B. I then applied your SUMIF formula and,
again, the result is zero. I have checked, double checked and
double-double
checked to make sure I don't have the formula wrong.

It is beyond me because, following your hyper-link, I could see that it
works for you.

Beckett
"T. Valko" wrote:

See this screencap:

http://img205.imageshack.us/img205/9712/sumifrx5.jpg

If you're getting a result of 0 then there's a problem with your data.
The
dollar amounts might be TEXT.

Try one of these:

Select the range of dollar amounts.
Goto DataText to Columns
Click Finish

Sometimes that'll convert TEXT numbers to numeric numbers.

If that doesn't work try this:

Select the range of dollar amounts
Goto FormatCellsNumber tabGENERALOK
Select an empty cell that has not been preformatted
Copy that cell: Goto EditCopy
Select the range of dollar amounts
Goto EditPaste SpecialAddOK

--
Biff
Microsoft Excel MVP


"Beckett" wrote in message
...

Thanks for that. Unfortunately it results in a zero sum.

Total Date Sold
9,619.95 21/04/2008
12,279.95 12/03/2008
7,769.95 12/02/2008
9,819.95 28/03/2008
12,439.95 20/03/2008
7,507.95
20,029.95

0.00

Whereas I was hoping for 27,537.90

Any other suggestion? Thanks

Beckett
"T. Valko" wrote:

Try this:

=SUMIF(B1:B10,"",A1:A10)

--
Biff
Microsoft Excel MVP


"Beckett" wrote in message
...
Excel 2003

I want to have the total at the bottom of column A include only
those
values
in column A that do NOT have a date in the corresponding cell in
column
B.

This will be a dynamic spreadsheet with column A representing
sums
outlaid
for purchases and column B the date upon which the item was sold.
The
spreadsheet is somewhat more complex than merely column A & B and
the
dynamic
total at the bottom of Column A is used in another formula.