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

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

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

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


.

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


.

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


.

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

.

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


.



.