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.

Hi SonjaLouise,

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

--
Cheers
macropod
[Microsoft MVP - Word]


"SonjaLouise" wrote in message ...
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.

Try

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

If this post helps click Yes
---------------
Jacob Skaria


"SonjaLouise" wrote:

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.

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" wrote:

Hi SonjaLouise,

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

--
Cheers
macropod
[Microsoft MVP - Word]


"SonjaLouise" wrote in message ...
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.

Thank you, yes this was very helpful.

"Jacob Skaria" wrote:

Try

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

If this post helps click Yes
---------------
Jacob Skaria


"SonjaLouise" wrote:

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.

Perhaps this link will help you out some...

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

--
Rick (MVP - Excel)


"SonjaLouise" wrote in message
...
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" wrote:

Hi SonjaLouise,

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

--
Cheers
macropod
[Microsoft MVP - Word]


"SonjaLouise" wrote in message
...
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.