IF formulas
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. |
IF formulas
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. |
IF formulas
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. |
IF formulas
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. |
IF formulas
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. |
IF formulas
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. |
All times are GMT +1. The time now is 01:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com