Using IF in formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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))
 
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")))
 
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
 
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.
 
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
 
Hi Frank

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

Regards

Mick
 
Back
Top