Home 
Search 
Today's Posts 
#1




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




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 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. 
#3




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 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. 
#4




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 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. 
#5




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 I looked and read about them, they're over my head. 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 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. 
#6




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 I looked and read about them, they're over my head. 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 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. 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Lookup cell contents in on sheet based on a formula in second sheet  Excel Worksheet Functions  
Cell will not format numbers correctly for a 13 digit custom barc.  Excel Worksheet Functions  
How do I find the contents of a cell using the "ADDRESS" function.  Excel Worksheet Functions  
make a cell empty based on condition  Charts and Charting in Excel  
Display actual contents of cell  Excel Discussion (Misc queries) 