| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF AND - need help with formula
I have to write a formula that accomplishes the following task:
I have a spreadsheet that has three columns. The columns are "Subs1" "Subs2" and "RATE1". Here's what I want it to do: If Subs1 = 0 and Subs2 = 0, "" If Subs2 0, sumproduct (subs2,rates1) If Subs1 0 and Subs2 = 0, sumproduct(subs1,rates1) I know this isn't all that difficult, but for some reason, it is not working for me. Thanks in advance! Kelly |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF AND - need help with formula
Kelly,
I might be being a bit slow but I don't understand the question. Can we see a couple of sample lines of data and the answer(s) you expect to get from that data? Mike "lsgKelly" wrote: I have to write a formula that accomplishes the following task: I have a spreadsheet that has three columns. The columns are "Subs1" "Subs2" and "RATE1". Here's what I want it to do: If Subs1 = 0 and Subs2 = 0, "" If Subs2 0, sumproduct (subs2,rates1) If Subs1 0 and Subs2 = 0, sumproduct(subs1,rates1) I know this isn't all that difficult, but for some reason, it is not working for me. Thanks in advance! Kelly |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF AND - need help with formula
Here's what I have, but it's returning a #Value error. It might help you
understand what I'm trying to do. =IF(AND(B41=0,J33=0),"",IF(AND(J330,B410),SUMPRO DUCT(J33:J37,I41:J45)*12,SUMPRODUCT(B41:B45,C41:F4 5)*12)) I have two cells, B41 and J33. If B41=0, then I want it to return a blank cell. If J33 is greater than 0, then I want it to do this part of the function: SUMPRODUCT(J33:J37,I41:J45)*12 If J33=0 but B410, then I want it to return this part of the function: SUMPRODUCT(B41:B45,C41:F45)*12 Hope that helps. :) "Mike H" wrote: Kelly, I might be being a bit slow but I don't understand the question. Can we see a couple of sample lines of data and the answer(s) you expect to get from that data? Mike "lsgKelly" wrote: I have to write a formula that accomplishes the following task: I have a spreadsheet that has three columns. The columns are "Subs1" "Subs2" and "RATE1". Here's what I want it to do: If Subs1 = 0 and Subs2 = 0, "" If Subs2 0, sumproduct (subs2,rates1) If Subs1 0 and Subs2 = 0, sumproduct(subs1,rates1) I know this isn't all that difficult, but for some reason, it is not working for me. Thanks in advance! Kelly |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF AND - need help with formula
may be this
=IF(AND(E32=0,F32=0),"",IF(F320,SUMPRODUCT(F32,G3 2),IF(AND(E320,F32=0),SUMPRODUCT(E32,G32),""))) On Sep 25, 2:37*pm, lsgKelly wrote: I have to write a formula that accomplishes the following task: I have a spreadsheet that has three columns. *The columns are "Subs1" "Subs2" and "RATE1". Here's what I want it to do: If Subs1 = 0 and Subs2 = 0, "" If Subs2 0, sumproduct (subs2,rates1) If Subs1 0 and Subs2 = 0, sumproduct(subs1,rates1) I know this isn't all that difficult, but for some reason, it is not working for me. Thanks in advance! Kelly |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF AND - need help with formula
Hi Kelly, maybe this -
=IF(AND(A2=0,B2=0),"",IF(B20,SUMPRODUCT(B2,C2),IF (AND(A20,B2=0),SUMPRODUCT(A2,C2),""))) Assumes your Sub1, Sub2 and Rate1 are columns A,B and C respectively and that your first line of data is on Row 2. Put that formula in D2(or wherever) and copy it down. Regards, Muppet Man. On Sep 25, 7:37*pm, lsgKelly wrote: I have to write a formula that accomplishes the following task: I have a spreadsheet that has three columns. *The columns are "Subs1" "Subs2" and "RATE1". Here's what I want it to do: If Subs1 = 0 and Subs2 = 0, "" If Subs2 0, sumproduct (subs2,rates1) If Subs1 0 and Subs2 = 0, sumproduct(subs1,rates1) I know this isn't all that difficult, but for some reason, it is not working for me. Thanks in advance! Kelly |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF AND - need help with formula
small change
=IF(AND(B41=0,J33=0),"",IF(AND(J330,B410),SUMPRO DUCT(J33:J37,I41:I45)*12,* SUMPRODUCT(B41:B45,C41:C45)*12)) SUMPRODUCT(J33:J37,I41:J45) change to SUMPRODUCT(J33:J37,I41:I45) On Sep 25, 3:01*pm, lsgKelly wrote: Here's what I have, but it's returning a #Value error. *It might help you understand what I'm trying to do. =IF(AND(B41=0,J33=0),"",IF(AND(J330,B410),SUMPRO DUCT(J33:J37,I41:J45)*12,*SUMPRODUCT(B41:B45,C41:F 45)*12)) I have two cells, B41 and J33. *If B41=0, then I want it to return a blank cell. *If J33 is greater than 0, then I want it to do this part of the function: SUMPRODUCT(J33:J37,I41:J45)*12 If J33=0 but B410, then I want it to return this part of the function: SUMPRODUCT(B41:B45,C41:F45)*12 Hope that helps. *:) "Mike H" wrote: Kelly, I might be being a bit slow but I don't understand the question. Can we see a couple of sample lines of data and the answer(s) you expect to get from that data? Mike "lsgKelly" wrote: I have to write a formula that accomplishes the following task: I have a spreadsheet that has three columns. *The columns are "Subs1" "Subs2" and "RATE1". Here's what I want it to do: If Subs1 = 0 and Subs2 = 0, "" If Subs2 0, sumproduct (subs2,rates1) If Subs1 0 and Subs2 = 0, sumproduct(subs1,rates1) I know this isn't all that difficult, but for some reason, it is not working for me. Thanks in advance! Kelly- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF AND - need help with formula
Now I get a number, but it's wrong. Is there a problem with using SumProduct
in Merged cells? The I41:I45 are two cells merged together. Thanks "muddan madhu" wrote: small change =IF(AND(B41=0,J33=0),"",IF(AND(J330,B410),SUMPRO DUCT(J33:J37,I41:I45)*12,Â* SUMPRODUCT(B41:B45,C41:C45)*12)) SUMPRODUCT(J33:J37,I41:J45) change to SUMPRODUCT(J33:J37,I41:I45) On Sep 25, 3:01 pm, lsgKelly wrote: Here's what I have, but it's returning a #Value error. It might help you understand what I'm trying to do. =IF(AND(B41=0,J33=0),"",IF(AND(J330,B410),SUMPRO DUCT(J33:J37,I41:J45)*12,Â*SUMPRODUCT(B41:B45,C41: F45)*12)) I have two cells, B41 and J33. If B41=0, then I want it to return a blank cell. If J33 is greater than 0, then I want it to do this part of the function: SUMPRODUCT(J33:J37,I41:J45)*12 If J33=0 but B410, then I want it to return this part of the function: SUMPRODUCT(B41:B45,C41:F45)*12 Hope that helps. :) "Mike H" wrote: Kelly, I might be being a bit slow but I don't understand the question. Can we see a couple of sample lines of data and the answer(s) you expect to get from that data? Mike "lsgKelly" wrote: I have to write a formula that accomplishes the following task: I have a spreadsheet that has three columns. The columns are "Subs1" "Subs2" and "RATE1". Here's what I want it to do: If Subs1 = 0 and Subs2 = 0, "" If Subs2 0, sumproduct (subs2,rates1) If Subs1 0 and Subs2 = 0, sumproduct(subs1,rates1) I know this isn't all that difficult, but for some reason, it is not working for me. Thanks in advance! Kelly- Hide quoted text - - Show quoted text - |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF AND - need help with formula
Save yourself a world of headaches and dont use Merged Cells, ever!
Take Care Muppet Man On Sep 25, 8:37*pm, lsgKelly wrote: Now I get a number, but it's wrong. *Is there a problem with using SumProduct in Merged cells? *The I41:I45 are two cells merged together. * Thanks "muddan madhu" wrote: small change =IF(AND(B41=0,J33=0),"",IF(AND(J330,B410),SUMPRO DUCT(J33:J37,I41:I45)*12,** SUMPRODUCT(B41:B45,C41:C45)*12)) SUMPRODUCT(J33:J37,I41:J45) change to SUMPRODUCT(J33:J37,I41:I45) On Sep 25, 3:01 pm, lsgKelly wrote: Here's what I have, but it's returning a #Value error. *It might help you understand what I'm trying to do. =IF(AND(B41=0,J33=0),"",IF(AND(J330,B410),SUMPRO DUCT(J33:J37,I41:J45)*12,**SUMPRODUCT(B41:B45,C41: F45)*12)) I have two cells, B41 and J33. *If B41=0, then I want it to return a blank cell. *If J33 is greater than 0, then I want it to do this part of the function: SUMPRODUCT(J33:J37,I41:J45)*12 If J33=0 but B410, then I want it to return this part of the function: SUMPRODUCT(B41:B45,C41:F45)*12 Hope that helps. *:) "Mike H" wrote: Kelly, I might be being a bit slow but I don't understand the question. Can we see a couple of sample lines of data and the answer(s) you expect to get from that data? Mike "lsgKelly" wrote: I have to write a formula that accomplishes the following task: I have a spreadsheet that has three columns. *The columns are "Subs1" "Subs2" and "RATE1". Here's what I want it to do: If Subs1 = 0 and Subs2 = 0, "" If Subs2 0, sumproduct (subs2,rates1) If Subs1 0 and Subs2 = 0, sumproduct(subs1,rates1) I know this isn't all that difficult, but for some reason, it is not working for me. Thanks in advance! Kelly- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF AND - need help with formula
Unfortunately, in this case, I have no choice.
"MuppetMan" wrote: Save yourself a world of headaches and dont use Merged Cells, ever! Take Care Muppet Man On Sep 25, 8:37 pm, lsgKelly wrote: Now I get a number, but it's wrong. Is there a problem with using SumProduct in Merged cells? The I41:I45 are two cells merged together. Thanks "muddan madhu" wrote: small change =IF(AND(B41=0,J33=0),"",IF(AND(J330,B410),SUMPRO DUCT(J33:J37,I41:I45)*12,Â*Â* SUMPRODUCT(B41:B45,C41:C45)*12)) SUMPRODUCT(J33:J37,I41:J45) change to SUMPRODUCT(J33:J37,I41:I45) On Sep 25, 3:01 pm, lsgKelly wrote: Here's what I have, but it's returning a #Value error. It might help you understand what I'm trying to do. =IF(AND(B41=0,J33=0),"",IF(AND(J330,B410),SUMPRO DUCT(J33:J37,I41:J45)*12,Â*Â*SUMPRODUCT(B41:B45,C4 1:F45)*12)) I have two cells, B41 and J33. If B41=0, then I want it to return a blank cell. If J33 is greater than 0, then I want it to do this part of the function: SUMPRODUCT(J33:J37,I41:J45)*12 If J33=0 but B410, then I want it to return this part of the function: SUMPRODUCT(B41:B45,C41:F45)*12 Hope that helps. :) "Mike H" wrote: Kelly, I might be being a bit slow but I don't understand the question. Can we see a couple of sample lines of data and the answer(s) you expect to get from that data? Mike "lsgKelly" wrote: I have to write a formula that accomplishes the following task: I have a spreadsheet that has three columns. The columns are "Subs1" "Subs2" and "RATE1". Here's what I want it to do: If Subs1 = 0 and Subs2 = 0, "" If Subs2 0, sumproduct (subs2,rates1) If Subs1 0 and Subs2 = 0, sumproduct(subs1,rates1) I know this isn't all that difficult, but for some reason, it is not working for me. Thanks in advance! Kelly- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF AND - need help with formula
May I ask why, I am a fairly advanced Excel user and I have never used
merged cells except maybe within the first year I used Excel as a simple database, the only time I get in contact with merged cells is when I remove the merged cells from workbooks I am trying to fix? -- Regards, Peo Sjoblom "lsgKelly" wrote in message ... Unfortunately, in this case, I have no choice. "MuppetMan" wrote: Save yourself a world of headaches and dont use Merged Cells, ever! Take Care Muppet Man On Sep 25, 8:37 pm, lsgKelly wrote: Now I get a number, but it's wrong. Is there a problem with using SumProduct in Merged cells? The I41:I45 are two cells merged together. Thanks "muddan madhu" wrote: small change =IF(AND(B41=0,J33=0),"",IF(AND(J330,B410),SUMPRO DUCT(J33:J37,I41:I45)*12,** SUMPRODUCT(B41:B45,C41:C45)*12)) SUMPRODUCT(J33:J37,I41:J45) change to SUMPRODUCT(J33:J37,I41:I45) On Sep 25, 3:01 pm, lsgKelly wrote: Here's what I have, but it's returning a #Value error. It might help you understand what I'm trying to do. =IF(AND(B41=0,J33=0),"",IF(AND(J330,B410),SUMPRO DUCT(J33:J37,I41:J45)*12,**SUMPRODUCT(B41:B45,C41: F45)*12)) I have two cells, B41 and J33. If B41=0, then I want it to return a blank cell. If J33 is greater than 0, then I want it to do this part of the function: SUMPRODUCT(J33:J37,I41:J45)*12 If J33=0 but B410, then I want it to return this part of the function: SUMPRODUCT(B41:B45,C41:F45)*12 Hope that helps. :) "Mike H" wrote: Kelly, I might be being a bit slow but I don't understand the question. Can we see a couple of sample lines of data and the answer(s) you expect to get from that data? Mike "lsgKelly" wrote: I have to write a formula that accomplishes the following task: I have a spreadsheet that has three columns. The columns are "Subs1" "Subs2" and "RATE1". Here's what I want it to do: If Subs1 = 0 and Subs2 = 0, "" If Subs2 0, sumproduct (subs2,rates1) If Subs1 0 and Subs2 = 0, sumproduct(subs1,rates1) I know this isn't all that difficult, but for some reason, it is not working for me. Thanks in advance! Kelly- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF AND - need help with formula
If a merged cell is the answer, you have asked yourself the wrong question.
:-( -- David Biddulph "lsgKelly" wrote in message ... Unfortunately, in this case, I have no choice. "MuppetMan" wrote: Save yourself a world of headaches and dont use Merged Cells, ever! Take Care Muppet Man On Sep 25, 8:37 pm, lsgKelly wrote: Now I get a number, but it's wrong. Is there a problem with using SumProduct in Merged cells? The I41:I45 are two cells merged together. Thanks "muddan madhu" wrote: small change =IF(AND(B41=0,J33=0),"",IF(AND(J330,B410),SUMPRO DUCT(J33:J37,I41:I45)*12,** SUMPRODUCT(B41:B45,C41:C45)*12)) SUMPRODUCT(J33:J37,I41:J45) change to SUMPRODUCT(J33:J37,I41:I45) On Sep 25, 3:01 pm, lsgKelly wrote: Here's what I have, but it's returning a #Value error. It might help you understand what I'm trying to do. =IF(AND(B41=0,J33=0),"",IF(AND(J330,B410),SUMPRO DUCT(J33:J37,I41:J45)*12,**SUMPRODUCT(B41:B45,C41: F45)*12)) I have two cells, B41 and J33. If B41=0, then I want it to return a blank cell. If J33 is greater than 0, then I want it to do this part of the function: SUMPRODUCT(J33:J37,I41:J45)*12 If J33=0 but B410, then I want it to return this part of the function: SUMPRODUCT(B41:B45,C41:F45)*12 Hope that helps. :) "Mike H" wrote: Kelly, I might be being a bit slow but I don't understand the question. Can we see a couple of sample lines of data and the answer(s) you expect to get from that data? Mike "lsgKelly" wrote: I have to write a formula that accomplishes the following task: I have a spreadsheet that has three columns. The columns are "Subs1" "Subs2" and "RATE1". Here's what I want it to do: If Subs1 = 0 and Subs2 = 0, "" If Subs2 0, sumproduct (subs2,rates1) If Subs1 0 and Subs2 = 0, sumproduct(subs1,rates1) I know this isn't all that difficult, but for some reason, it is not working for me. Thanks in advance! Kelly- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
