IF next cell > 0 then display results for this cell

[ianfav]

Hi,
This is probably an easy one but for some reason I am unable to get the syntax correct. I have a formula that checks worksheets for a specific value in a column between certain rows. If the value is true then count as per below:

=COUNTIF('1 MMM-YY'!E8:E29,"SET")+COUNTIF('2 MMM-YY'!E8:E29,"SET")+COUNTIF('3 MMM-YY'!E8:E29,"SET")+COUNTIF('4 MMM-YY'!E8:E29,"SET")+COUNTIF('5 MMM-YY'!E8:E29,"SET")+COUNTIF('6 MMM-YY'!E8:E29,"SET")+COUNTIF('7 MMM-YY'!E8:E29,"SET")

The results from this formula are computed to a cell in a Summary worksheet, this formula works fine but my problem is that I want to only display the results in this cell when the cell next to it has a value >0.

 

[acw]

Ian

Assuming that the cell you want to test is B1 then try

=if(b1>0,COUNTIF('1 MMM-YY'!E8:E29,"SET")+COUNTIF('2 MMM-YY'!E8:E29,"SET")+COUNTIF('3 MMM-YY'!E8:E29,"SET")+COUNTIF('4 MMM-YY'!E8:E29,"SET")+COUNTIF('5 MMM-YY'!E8:E29,"SET")+COUNTIF('6 MMM-YY'!E8:E29,"SET")+COUNTIF('7 MMM-YY'!E8:E29,"SET") ,"")


Tony

 

[ianfav]

GR8, thanks Tony, works a treat.
I forgot the ,"") so I was always getting FALSE displayed in the result cell.

 

[Brian from Maui]

See,

http://www.mrexcel.com/board2/viewtopic.php?t=237332

If you have the Morefunc addin, use the COUNTIF.3D function.