If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using IF in formula
I am trying to build up a YTD value in a cell on a
worksheet named YTD. The YTD value is the sum of cell ref "A17" on each worksheet named JAN, FEB, MAR...etc upto DEC. The value of "A17" is only taken into consideration if the value of cell "R27" on the respective worksheets is greater than "0". eg. if cell "R27" is value 0 on w/sheet MAR then the YTD value is the sum of cells "A17" on w/sheets JAN and FEB, if the value is 0 on w/sheet APR then the YTD is the sum of cells "A17" on w/sheets JAN, FEB and MAR...and so on. I am using the following formula: =IF(FEB!R270,SUM(JAN!R17),0) This works fine but when I add the following to the formula it does not do the correct calculation. =IF(FEB!R270,SUM(JAN!R17),0),IF(MAR!R270,SUM(JAN !R17+FEB! R17)) Could some kind person please tell me what I am doing wrong and also tell me if there is another way of acheiving what I want. Thanks |
#2
|
|||
|
|||
Using IF in formula
Hi
In my opinion (maybe not worth much) I would have a summary table (hidden if necesary) on your YTD sheet. =JAN!A17 =JAN!R27 =FEB!A17 =FEB!R27 etc Once this is done, you can just SUMPRODUCT the whole table: =SUMPRODUCT((A2:A11)*(B2:B110)) -- Andy. wrote in message ... I am trying to build up a YTD value in a cell on a worksheet named YTD. The YTD value is the sum of cell ref "A17" on each worksheet named JAN, FEB, MAR...etc upto DEC. The value of "A17" is only taken into consideration if the value of cell "R27" on the respective worksheets is greater than "0". eg. if cell "R27" is value 0 on w/sheet MAR then the YTD value is the sum of cells "A17" on w/sheets JAN and FEB, if the value is 0 on w/sheet APR then the YTD is the sum of cells "A17" on w/sheets JAN, FEB and MAR...and so on. I am using the following formula: =IF(FEB!R270,SUM(JAN!R17),0) This works fine but when I add the following to the formula it does not do the correct calculation. =IF(FEB!R270,SUM(JAN!R17),0),IF(MAR!R270,SUM(JAN !R17+FEB! R17)) Could some kind person please tell me what I am doing wrong and also tell me if there is another way of acheiving what I want. Thanks |
#4
|
|||
|
|||
Using IF in formula
Thanks for the option Andy.
However I dont think it's going to be of any use as I am not multiplying A17 by R27, rather Im saying that if R27 is greater than value 0 then add the values in A17 of the w'sheets that the IF statement is true for. Using your example I would still have to define what values in A2:A11 should be added dependent on the value in B2:B11. Thanks anyway Regards -----Original Message----- Hi In my opinion (maybe not worth much) I would have a summary table (hidden if necesary) on your YTD sheet. =JAN!A17 =JAN!R27 =FEB!A17 =FEB!R27 etc Once this is done, you can just SUMPRODUCT the whole table: =SUMPRODUCT((A2:A11)*(B2:B110)) -- Andy. wrote in message ... I am trying to build up a YTD value in a cell on a worksheet named YTD. The YTD value is the sum of cell ref "A17" on each worksheet named JAN, FEB, MAR...etc upto DEC. The value of "A17" is only taken into consideration if the value of cell "R27" on the respective worksheets is greater than "0". eg. if cell "R27" is value 0 on w/sheet MAR then the YTD value is the sum of cells "A17" on w/sheets JAN and FEB, if the value is 0 on w/sheet APR then the YTD is the sum of cells "A17" on w/sheets JAN, FEB and MAR...and so on. I am using the following formula: =IF(FEB!R270,SUM(JAN!R17),0) This works fine but when I add the following to the formula it does not do the correct calculation. =IF(FEB!R270,SUM(JAN!R17),0),IF(MAR!R270,SUM(JAN ! R17+FEB! R17)) Could some kind person please tell me what I am doing wrong and also tell me if there is another way of acheiving what I want. Thanks . |
#5
|
|||
|
|||
Using IF in formula
No. The formula I sent will only include the values if the R27 value is
above zero. How much above zero is irelevant. Try it and you'll see. -- Andy. wrote in message ... Thanks for the option Andy. However I dont think it's going to be of any use as I am not multiplying A17 by R27, rather Im saying that if R27 is greater than value 0 then add the values in A17 of the w'sheets that the IF statement is true for. Using your example I would still have to define what values in A2:A11 should be added dependent on the value in B2:B11. Thanks anyway Regards -----Original Message----- Hi In my opinion (maybe not worth much) I would have a summary table (hidden if necesary) on your YTD sheet. =JAN!A17 =JAN!R27 =FEB!A17 =FEB!R27 etc Once this is done, you can just SUMPRODUCT the whole table: =SUMPRODUCT((A2:A11)*(B2:B110)) -- Andy. wrote in message ... I am trying to build up a YTD value in a cell on a worksheet named YTD. The YTD value is the sum of cell ref "A17" on each worksheet named JAN, FEB, MAR...etc upto DEC. The value of "A17" is only taken into consideration if the value of cell "R27" on the respective worksheets is greater than "0". eg. if cell "R27" is value 0 on w/sheet MAR then the YTD value is the sum of cells "A17" on w/sheets JAN and FEB, if the value is 0 on w/sheet APR then the YTD is the sum of cells "A17" on w/sheets JAN, FEB and MAR...and so on. I am using the following formula: =IF(FEB!R270,SUM(JAN!R17),0) This works fine but when I add the following to the formula it does not do the correct calculation. =IF(FEB!R270,SUM(JAN!R17),0),IF(MAR!R270,SUM(JAN ! R17+FEB! R17)) Could some kind person please tell me what I am doing wrong and also tell me if there is another way of acheiving what I want. Thanks . |
#6
|
|||
|
|||
Using IF in formula
Hi
Andy's SUMPRODUCT formula would just do what (after creating the summary table of course). Though in this case a simple SUMIF would do :-) =SUMIF(B2:B11,"0",A2:A11) For a solution without a summary sheet see my other post -- Regards Frank Kabel Frankfurt, Germany wrote: Thanks for the option Andy. However I dont think it's going to be of any use as I am not multiplying A17 by R27, rather Im saying that if R27 is greater than value 0 then add the values in A17 of the w'sheets that the IF statement is true for. Using your example I would still have to define what values in A2:A11 should be added dependent on the value in B2:B11. Thanks anyway Regards -----Original Message----- Hi In my opinion (maybe not worth much) I would have a summary table (hidden if necesary) on your YTD sheet. =JAN!A17 =JAN!R27 =FEB!A17 =FEB!R27 etc Once this is done, you can just SUMPRODUCT the whole table: =SUMPRODUCT((A2:A11)*(B2:B110)) -- Andy. wrote in message ... I am trying to build up a YTD value in a cell on a worksheet named YTD. The YTD value is the sum of cell ref "A17" on each worksheet named JAN, FEB, MAR...etc upto DEC. The value of "A17" is only taken into consideration if the value of cell "R27" on the respective worksheets is greater than "0". eg. if cell "R27" is value 0 on w/sheet MAR then the YTD value is the sum of cells "A17" on w/sheets JAN and FEB, if the value is 0 on w/sheet APR then the YTD is the sum of cells "A17" on w/sheets JAN, FEB and MAR...and so on. I am using the following formula: =IF(FEB!R270,SUM(JAN!R17),0) This works fine but when I add the following to the formula it does not do the correct calculation. =IF(FEB!R270,SUM(JAN!R17),0),IF(MAR!R270,SUM(JAN ! R17+FEB! R17)) Could some kind person please tell me what I am doing wrong and also tell me if there is another way of acheiving what I want. Thanks . |
#7
|
|||
|
|||
Using IF in formula
Hi Frank
Thanks for your suggestions. I have implemented it and it is now working fine. Regards Mick -----Original Message----- Hi try the following: 1. Create a range with all your sheet names. e.g. put the names Jan, Feb, etc in a range and define a name ('Insert - Name - Define' for this range. e.g. name this list of worksheet names 'wslist 2. Now use the following formula =SUMPRODUCT(SUMIF(INDIRECT("'" & wslist & "'! R27"),"0",INDIREKT("'" & wslist & "'!A17"))) Note: if you have named your worksheets 'Jan' to 'Dec' you could also use a formula like =SUMPRODUCT(SUMIF(INDIRECT("'" & TEXT("2004-" & ROW (INDIRECT("1:12")) & "-1","MMM") & "'!R27"),"0",INDIRECT("'" & TEXT("2004-" & ROW(INDIRECT("1:12")) & "-1","MMM") & "'!A17"))) -- Regards Frank Kabel Frankfurt, Germany wrote: I am trying to build up a YTD value in a cell on a worksheet named YTD. The YTD value is the sum of cell ref "A17" on each worksheet named JAN, FEB, MAR...etc upto DEC. The value of "A17" is only taken into consideration if the value of cell "R27" on the respective worksheets is greater than "0". eg. if cell "R27" is value 0 on w/sheet MAR then the YTD value is the sum of cells "A17" on w/sheets JAN and FEB, if the value is 0 on w/sheet APR then the YTD is the sum of cells "A17" on w/sheets JAN, FEB and MAR...and so on. I am using the following formula: =IF(FEB!R270,SUM(JAN!R17),0) This works fine but when I add the following to the formula it does not do the correct calculation. =IF(FEB!R270,SUM(JAN!R17),0),IF(MAR!R270,SUM(JAN ! R17+FEB! R17)) Could some kind person please tell me what I am doing wrong and also tell me if there is another way of acheiving what I want. Thanks . |
#8
|
|||
|
|||
Using IF in formula
Hi Andy.
Thanks, I have tried it and I now see what you mean. Regards Mick -----Original Message----- No. The formula I sent will only include the values if the R27 value is above zero. How much above zero is irelevant. Try it and you'll see. -- Andy. wrote in message ... Thanks for the option Andy. However I dont think it's going to be of any use as I am not multiplying A17 by R27, rather Im saying that if R27 is greater than value 0 then add the values in A17 of the w'sheets that the IF statement is true for. Using your example I would still have to define what values in A2:A11 should be added dependent on the value in B2:B11. Thanks anyway Regards -----Original Message----- Hi In my opinion (maybe not worth much) I would have a summary table (hidden if necesary) on your YTD sheet. =JAN!A17 =JAN!R27 =FEB!A17 =FEB!R27 etc Once this is done, you can just SUMPRODUCT the whole table: =SUMPRODUCT((A2:A11)*(B2:B110)) -- Andy. wrote in message ... I am trying to build up a YTD value in a cell on a worksheet named YTD. The YTD value is the sum of cell ref "A17" on each worksheet named JAN, FEB, MAR...etc upto DEC. The value of "A17" is only taken into consideration if the value of cell "R27" on the respective worksheets is greater than "0". eg. if cell "R27" is value 0 on w/sheet MAR then the YTD value is the sum of cells "A17" on w/sheets JAN and FEB, if the value is 0 on w/sheet APR then the YTD is the sum of cells "A17" on w/sheets JAN, FEB and MAR...and so on. I am using the following formula: =IF(FEB!R270,SUM(JAN!R17),0) This works fine but when I add the following to the formula it does not do the correct calculation. =IF(FEB!R270,SUM(JAN!R17),0),IF(MAR!R270,SUM(JAN ! R17+FEB! R17)) Could some kind person please tell me what I am doing wrong and also tell me if there is another way of acheiving what I want. Thanks . . |
Thread Tools | |
Display Modes | |
|
|