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

The simplest method is this: =IF(COUNTIF(E3:AK3,"No")=33, A1, 0) since there are 33 columns in E3:AK3

Another 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:AK3

If 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.

Ask Your Question

Weekly Poll

Do you think Jony Ive could make a big impact on Airbnb?

Discuss in The Lounge

Poll History