IF formulas

S

SonjaLouise

I have a spreadsheet that contains three colums , one that contains text such
as "AP01, AP02, AP03), the second containing text such as "LJA014, NHA003",
and the third containing the associated costs. I am trying to create a
formula that will add the costs of the third column to give me one overall
cost, only if the conditions in both the text columns agree. I have tried the
following -

=IF((M33:M1000)="LJA14",IF((W33:W1000)="AP01",SUM(V33:V1000),0),0)

but this gives me a #value error.

Is what I am trying to do possible, and if so how can I amend this to make
it work.
 
M

macropod

Hi SonjaLouise,

Try: =SUMPRODUCT((M33:M1000="LJA14")*(W33:W1000="AP01"),V33:V1000)
 
J

Jacob Skaria

Try

=SUMPRODUCT(--(M33:M1000="LJA14"),--(W33:W1000="AP01"),V33:V1000)

If this post helps click Yes
 
S

SonjaLouise

Thank you, this has proved very helpful. I,ve never used sumproduct before,
but I'm sure I'm going to get a lot of use from it now.

macropod said:
Hi SonjaLouise,

Try: =SUMPRODUCT((M33:M1000="LJA14")*(W33:W1000="AP01"),V33:V1000)

--
Cheers
macropod
[Microsoft MVP - Word]


SonjaLouise said:
I have a spreadsheet that contains three colums , one that contains text such
as "AP01, AP02, AP03), the second containing text such as "LJA014, NHA003",
and the third containing the associated costs. I am trying to create a
formula that will add the costs of the third column to give me one overall
cost, only if the conditions in both the text columns agree. I have tried the
following -

=IF((M33:M1000)="LJA14",IF((W33:W1000)="AP01",SUM(V33:V1000),0),0)

but this gives me a #value error.

Is what I am trying to do possible, and if so how can I amend this to make
it work.
Click to expand...
Click to expand...
 
S

SonjaLouise

Thank you, yes this was very helpful.

Jacob Skaria said:
Try

=SUMPRODUCT(--(M33:M1000="LJA14"),--(W33:W1000="AP01"),V33:V1000)

If this post helps click Yes
Click to expand...
 
R

Rick Rothstein

Perhaps this link will help you out some...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Rick (MVP - Excel)


SonjaLouise said:
Thank you, this has proved very helpful. I,ve never used sumproduct
before,
but I'm sure I'm going to get a lot of use from it now.

macropod said:
Hi SonjaLouise,

Try: =SUMPRODUCT((M33:M1000="LJA14")*(W33:W1000="AP01"),V33:V1000)

--
Cheers
macropod
[Microsoft MVP - Word]


SonjaLouise said:
I have a spreadsheet that contains three colums , one that contains text
such
as "AP01, AP02, AP03), the second containing text such as "LJA014,
NHA003",
and the third containing the associated costs. I am trying to create a
formula that will add the costs of the third column to give me one
overall
cost, only if the conditions in both the text columns agree. I have
tried the
following -

=IF((M33:M1000)="LJA14",IF((W33:W1000)="AP01",SUM(V33:V1000),0),0)

but this gives me a #value error.

Is what I am trying to do possible, and if so how can I amend this to
make
it work.
Click to expand...
Click to expand...
Click to expand...
 
Top