ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   IF AND - need help with formula (https://www.excelbanter.com/excel-worksheet-functions/204004-if-need-help-formula.html)

lsgKelly

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



Mike H

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



lsgKelly

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



muddan madhu

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



[email protected]

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



muddan madhu

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 -



lsgKelly

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 -




MuppetMan

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 -



lsgKelly

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 -




Peo Sjoblom[_2_]

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 -






David Biddulph[_2_]

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 -







All times are GMT +1. The time now is 07:24 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com