# IF Statement for Excel

Microsoft Excel 2003 (full product)
September 29, 2010 at 05:44:16
Specs: Windows XP
 Hi :)I'm having problems using an IF Statement.I have a spreadsheet with multiple lines of data. I need a formula that starts in cell D3 and searches E3 thru AK3, to see if they all contain 'No', if they do i want it to return the value in A3. If any of the value's says 'Yes', then i want it to return a 0.I was thinking it might looks something like:=IF(COUNTIF(E3:AK3,"No") A3, 0)i thought this would be simple to do, but am feeling lost, any help you could give me would be very welcome.thank you so much

See More: IF Statement for Excel

#1
September 29, 2010 at 06:52:58
 The simplest method is this:=IF(COUNTIF(E3:AK3,"No")=33, A1, 0) since there are 33 columns in E3:AK3Another method is this, which uses the COLUMNS function to return the number of columns in a range, which in this case is 33.=IF(COUNTIF(E3:AK3,"No")=COLUMNS(E3:AK3),A1,0)You could also use this, as long as there are no blank cells in E3:AK3If there will never be any blank cells in E3:AK3, this should work also:=IF(COUNTIF(E3:AK3,"No")=COUNTA(E3:AK3),A1,0)COUNTA counts the number of cells that contain data, so if it is equal to the number of cells that contain "No", then the IF condition is TRUE. However, if there are any blank cells in E3:AK3, this formula won't get you the results you want.

Report •
Related Solutions