Is SUMIF the function I need and if so, how do I formulate it?

[Beckett]

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.

 

[T. Valko]

Try this:

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

 

[Beckett]

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]

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 Data>Text 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 Format>Cells>Number tab>GENERAL>OK
Select an empty cell that has not been preformatted
Copy that cell: Goto Edit>Copy
Select the range of dollar amounts
Goto Edit>Paste Special>Add>OK

 

[Beckett]

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 number>general, 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]

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)

 

[Beckett]

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]

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*.