Remember Me?

#1
May 4th 05, 03:51 PM
 Doreen Posts: n/a
Sum numbers based on the contents of another cell

I have a simple spreadsheet with one column showing amounts owed that I need
to bill. At the bottom of the column I sum the total amounts. In the adjacent
column I've started to put the invoice numbers for the ones that have been
billed. I'd like to write a formula that would:
Look at "column e" and
if it's not empty (meaning there's no invoice # there)
add the amount given in "column d"

This way I can subtract that total amount (which has been billed) from the
total amount owed at the bottom...that's where I would like to put the result
of this formula.

I'm sorry if I'm not explaining this well and I thank everyone for their help.

#2
May 4th 05, 04:00 PM
 JulieD Posts: n/a

Hi Doreen

only slightly confused by the sentance
if it's not empty (meaning there's no invoice # there)

this formula will sum column D if there is something in in column E
=SUMIF(E:E,"<"&"",D)

this formula will sum column D if there is nothing in column E
=SUMIF(E:E,"",D)

(the "" are two double quotes with no space between)

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Doreen" wrote in message
...
I have a simple spreadsheet with one column showing amounts owed that I
need
to bill. At the bottom of the column I sum the total amounts. In the
column I've started to put the invoice numbers for the ones that have been
billed. I'd like to write a formula that would:
Look at "column e" and
if it's not empty (meaning there's no invoice # there)
add the amount given in "column d"

This way I can subtract that total amount (which has been billed) from the
total amount owed at the bottom...that's where I would like to put the
result
of this formula.

I'm sorry if I'm not explaining this well and I thank everyone for their
help.

#3
May 4th 05, 07:54 PM
 Doreen Posts: n/a

Julie, thanks for your help, but I don't think that's quite it. If I
understand your formulas, they're adding up all the amounts in column D (or
not). Hmmm let me try this again:
I have a column with \$ amounts in it (D) and a column where I'm typing
invoice #s when the money owed is billed (E).

I would like the formula to look at *one* cell in column e and, if there's
an invoice # in it (text) add the corresponding \$ amount shown in column D.

This way I can manipulate information...how much has been billed, still
needs to be billed.
Any help?
Doreen

"JulieD" wrote:

Hi Doreen

only slightly confused by the sentance
if it's not empty (meaning there's no invoice # there)

this formula will sum column D if there is something in in column E
=SUMIF(E:E,"<"&"",D)

this formula will sum column D if there is nothing in column E
=SUMIF(E:E,"",D)

(the "" are two double quotes with no space between)

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Doreen" wrote in message
...
I have a simple spreadsheet with one column showing amounts owed that I
need
to bill. At the bottom of the column I sum the total amounts. In the
column I've started to put the invoice numbers for the ones that have been
billed. I'd like to write a formula that would:
Look at "column e" and
if it's not empty (meaning there's no invoice # there)
add the amount given in "column d"

This way I can subtract that total amount (which has been billed) from the
total amount owed at the bottom...that's where I would like to put the
result
of this formula.

I'm sorry if I'm not explaining this well and I thank everyone for their
help.

#4
May 5th 05, 01:23 PM
 JulieD Posts: n/a

Hi Doreen

what do you mean by "add the corresponding \$ amount shown in column D"
say you have D4 with \$100, in E4 you have an Invoice Number

where/what do you want this \$100 to be added to?

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Doreen" wrote in message
...
Julie, thanks for your help, but I don't think that's quite it. If I
understand your formulas, they're adding up all the amounts in column D
(or
not). Hmmm let me try this again:
I have a column with \$ amounts in it (D) and a column where I'm typing
invoice #s when the money owed is billed (E).

I would like the formula to look at *one* cell in column e and, if there's
an invoice # in it (text) add the corresponding \$ amount shown in column
D.

This way I can manipulate information...how much has been billed, still
needs to be billed.
Any help?
Doreen

"JulieD" wrote:

Hi Doreen

only slightly confused by the sentance
if it's not empty (meaning there's no invoice # there)

this formula will sum column D if there is something in in column E
=SUMIF(E:E,"<"&"",D)

this formula will sum column D if there is nothing in column E
=SUMIF(E:E,"",D)

(the "" are two double quotes with no space between)

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Doreen" wrote in message
...
I have a simple spreadsheet with one column showing amounts owed that I
need
to bill. At the bottom of the column I sum the total amounts. In the
column I've started to put the invoice numbers for the ones that have
been
billed. I'd like to write a formula that would:
Look at "column e" and
if it's not empty (meaning there's no invoice # there)
add the amount given in "column d"

This way I can subtract that total amount (which has been billed) from
the
total amount owed at the bottom...that's where I would like to put the
result
of this formula.

I'm sorry if I'm not explaining this well and I thank everyone for
their
help.

#5
May 5th 05, 03:52 PM
 Doreen Posts: n/a

Well, this is a secondary problem because the first cell "D4" won't have
another number added to it. So can I use "0"...that wouldn't change the
result of the formula....

Again...I have a column D with dollar amounts in it...and column E with
invoice numbers and blank cells. I'd like to look at column E and
say:"Invoice in column E, add corresponding \$amount in column D. No invoice
shown? Skip it."

I don't care where the total/sum of all \$amounts for invoices is put.

I think it might have to be a nested IF or an array formula but, even though

If you can point me in the right direction I'll be thrilled to try and work
along with you. I feel bad that you're working so hard...I feel like I'm
"dumping" this on you.
I really appreciate your help and the chance to learn something more about
formulas.
Doreen

"JulieD" wrote:

Hi Doreen

what do you mean by "add the corresponding \$ amount shown in column D"
say you have D4 with \$100, in E4 you have an Invoice Number

where/what do you want this \$100 to be added to?

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Doreen" wrote in message
...
Julie, thanks for your help, but I don't think that's quite it. If I
understand your formulas, they're adding up all the amounts in column D
(or
not). Hmmm let me try this again:
I have a column with \$ amounts in it (D) and a column where I'm typing
invoice #s when the money owed is billed (E).

I would like the formula to look at *one* cell in column e and, if there's
an invoice # in it (text) add the corresponding \$ amount shown in column
D.

This way I can manipulate information...how much has been billed, still
needs to be billed.
Any help?
Doreen

"JulieD" wrote:

Hi Doreen

only slightly confused by the sentance
if it's not empty (meaning there's no invoice # there)

this formula will sum column D if there is something in in column E
=SUMIF(E:E,"<"&"",D)

this formula will sum column D if there is nothing in column E
=SUMIF(E:E,"",D)

(the "" are two double quotes with no space between)

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Doreen" wrote in message
...
I have a simple spreadsheet with one column showing amounts owed that I
need
to bill. At the bottom of the column I sum the total amounts. In the
column I've started to put the invoice numbers for the ones that have
been
billed. I'd like to write a formula that would:
Look at "column e" and
if it's not empty (meaning there's no invoice # there)
add the amount given in "column d"

This way I can subtract that total amount (which has been billed) from
the
total amount owed at the bottom...that's where I would like to put the
result
of this formula.

I'm sorry if I'm not explaining this well and I thank everyone for
their
help.

#6
May 5th 05, 04:41 PM
 JulieD Posts: n/a

Hi Doreen

sorry i keep coming back to the original formulas i gave you - have you
tried them?

alternatively, do you want to email me your workbook so i can have a look -
send it direct to julied_ng at hcts dot net dot au , it's now 11.40pm so
i probably won't get to it until tomorrow.

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Doreen" wrote in message
...
Well, this is a secondary problem because the first cell "D4" won't have
another number added to it. So can I use "0"...that wouldn't change the
result of the formula....

Again...I have a column D with dollar amounts in it...and column E with
invoice numbers and blank cells. I'd like to look at column E and
say:"Invoice in column E, add corresponding \$amount in column D. No
invoice
shown? Skip it."

I don't care where the total/sum of all \$amounts for invoices is put.

I think it might have to be a nested IF or an array formula but, even
though

If you can point me in the right direction I'll be thrilled to try and
work
along with you. I feel bad that you're working so hard...I feel like I'm
"dumping" this on you.
I really appreciate your help and the chance to learn something more about
formulas.
Doreen

"JulieD" wrote:

Hi Doreen

what do you mean by "add the corresponding \$ amount shown in column D"
say you have D4 with \$100, in E4 you have an Invoice Number

where/what do you want this \$100 to be added to?

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Doreen" wrote in message
...
Julie, thanks for your help, but I don't think that's quite it. If I
understand your formulas, they're adding up all the amounts in column D
(or
not). Hmmm let me try this again:
I have a column with \$ amounts in it (D) and a column where I'm typing
invoice #s when the money owed is billed (E).

I would like the formula to look at *one* cell in column e and, if
there's
an invoice # in it (text) add the corresponding \$ amount shown in
column
D.

This way I can manipulate information...how much has been billed, still
needs to be billed.
Any help?
Doreen

"JulieD" wrote:

Hi Doreen

only slightly confused by the sentance
if it's not empty (meaning there's no invoice # there)

this formula will sum column D if there is something in in column E
=SUMIF(E:E,"<"&"",D)

this formula will sum column D if there is nothing in column E
=SUMIF(E:E,"",D)

(the "" are two double quotes with no space between)

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Doreen" wrote in message
...
I have a simple spreadsheet with one column showing amounts owed that
I
need
to bill. At the bottom of the column I sum the total amounts. In the
column I've started to put the invoice numbers for the ones that
have
been
billed. I'd like to write a formula that would:
Look at "column e" and
if it's not empty (meaning there's no invoice # there)
add the amount given in "column d"

This way I can subtract that total amount (which has been billed)
from
the
total amount owed at the bottom...that's where I would like to put
the
result
of this formula.

I'm sorry if I'm not explaining this well and I thank everyone for
their
help.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Michael Wright via OfficeKB.com Excel Worksheet Functions 1 April 30th 05 04:11 PM Laudan Excel Worksheet Functions 4 April 11th 05 08:13 PM sweeney Excel Worksheet Functions 2 April 5th 05 03:23 AM mpierre Charts and Charting in Excel 2 December 29th 04 01:01 PM xmasbob Excel Discussion (Misc queries) 1 December 6th 04 05:09 PM

All times are GMT +1. The time now is 03:05 PM.

The comments are property of their posters.