Using IF in formula

[Guest]

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!R27>0,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!R27>0,SUM(JAN!R17),0),IF(MAR!R27>0,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

 

[Andy B]

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:B11>0))

 

[Frank Kabel]

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")))

 

[Guest]

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

 

[Andy B]

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.

 

[Frank Kabel]

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

 

[Guest]

Hi Frank

Thanks for your suggestions. I have implemented it and it
is now working fine.

Regards

Mick

 

[Guest]

Hi Andy.

Thanks, I have tried it and I now see what you mean.

Regards

Mick