IF AND - need help with formula

L

lsgKelly

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
 
M

Mike H

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
 
L

lsgKelly

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(J33>0,B41>0),SUMPRODUCT(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 B41>0, then I want it to return this part of the function:
SUMPRODUCT(B41:B45,C41:F45)*12

Hope that helps. :)
 
M

muddan madhu

may be this

=IF(AND(E32=0,F32=0),"",IF(F32>0,SUMPRODUCT(F32,G32),IF(AND(E32>0,F32=0),SUMPRODUCT(E32,G32),"")))
 
A

a.muppet.man

Hi Kelly, maybe this -

=IF(AND(A2=0,B2=0),"",IF(B2>0,SUMPRODUCT(B2,C2),IF(AND(A2>0,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.
 
M

muddan madhu

small change

=IF(AND(B41=0,J33=0),"",IF(AND(J33>0,B41>0),SUMPRODUCT(J33:J37,I41:I45)*12,­
SUMPRODUCT(B41:B45,C41:C45)*12))


SUMPRODUCT(J33:J37,I41:J45) change to SUMPRODUCT(J33:J37,I41:I45)
 
L

lsgKelly

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
 
M

MuppetMan

Save yourself a world of headaches and dont use Merged Cells, ever!

Take Care
Muppet Man
 
P

Peo Sjoblom

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top