Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

Need an equation which can be applied to a column. Basic equation is =IF H1="yes",0,1 It only applies to one cell but I need to make it refer to the whole column. So IF H1,H2,H3 H4 etc = "yes",0,1. However I don't know how to express this mathematicallly
Solved by M. F. in 16 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 10/11/2017 - 03:58
Hello :)
User 10/11/2017 - 03:58
hello
Excelchat Expert 10/11/2017 - 03:58
How many rows will this formula cover?
User 10/11/2017 - 03:59
no rows, only column
Excelchat Expert 10/11/2017 - 03:59
I see.
Excelchat Expert 10/11/2017 - 03:59
So will it be H1 = yes and H2 = yes, the toal will be .2?
User 10/11/2017 - 03:59
I guess something like (A1, A(1+n) where n is infinite numbet
Excelchat Expert 10/11/2017 - 03:59
Or is it if any of them = yes then, .1 is the result?
User 10/11/2017 - 04:00
yes=1, no=0
User 10/11/2017 - 04:00
formula need to be specific to column but not to the cell, true for any cell
Excelchat Expert 10/11/2017 - 04:00
Okay, but the result can be any number depending on how many cells in column H = Yes?
User 10/11/2017 - 04:00
gonna be 500 cells
User 10/11/2017 - 04:00
yes
Excelchat Expert 10/11/2017 - 04:01
Perfect. One moment please.
User 10/11/2017 - 04:02
the result depends on the number of cells in colum A or b or c or d. It's the sum of the column I'm seeking, need to convert word "yes" to 1 and "no " to 0
Excelchat Expert 10/11/2017 - 04:02
=countif(H1:H,"Yes")
Excelchat Expert 10/11/2017 - 04:02
That is the formula in cell I1
User 10/11/2017 - 04:03
No this does not help
Excelchat Expert 10/11/2017 - 04:04
I'm sorry. I don't understand. Why doesn't it help. 1 Yes = 1 correct?
Excelchat Expert 10/11/2017 - 04:04
Can you maybe show me an example of what you are looking for?
User 10/11/2017 - 04:04
what's it doing in column I? I don't want it ther, want vertical total of column H, or J, or A, or F
Excelchat Expert 10/11/2017 - 04:05
In column I it is counting the number of Yes responses.
Excelchat Expert 10/11/2017 - 04:05
That is a vertical total of all the Yes cells in column H.
User 10/11/2017 - 04:05
So you have to have a separate coulum to total it?
User 10/11/2017 - 04:05
Why not total it at the bottom of H? can't do?
Excelchat Expert 10/11/2017 - 04:05
doesn't have to be a separate column, but it has to be in a separate cell.
Excelchat Expert 10/11/2017 - 04:06
That can be done as well.
Excelchat Expert 10/11/2017 - 04:06
The formula can go in any cell and get the same answer.
User 10/11/2017 - 04:06
Hoq do you tell it which cell to use
Excelchat Expert 10/11/2017 - 04:06
I was just putting it beside so that you could see the result and how the result was calculated easier.
User 10/11/2017 - 04:06
I see
Excelchat Expert 10/11/2017 - 04:06
As long as you copy that formula, you can use it anywhere.
User 10/11/2017 - 04:06
How can I tell the program which cell to use?
Excelchat Expert 10/11/2017 - 04:07
You paste the formula in the cell you want it to use.
Excelchat Expert 10/11/2017 - 04:07
For example, I'll paste the formula in H1.
User 10/11/2017 - 04:07
Not in the H column
Excelchat Expert 10/11/2017 - 04:07
What column do you want the answer to be in?
User 10/11/2017 - 04:07
at the bottom of H
Excelchat Expert 10/11/2017 - 04:08
Please see H22
User 10/11/2017 - 04:08
so just put the formula in the last cell?
Excelchat Expert 10/11/2017 - 04:08
Yes, but you will have to adjust the formula in order to do this.
User 10/11/2017 - 04:08
THat's so cool
Excelchat Expert 10/11/2017 - 04:09
the last H in the formula will need to be changed to H500 or if the formula is in cell H501
Excelchat Expert 10/11/2017 - 04:09
or in this case, It is in H22, so the last H is converted to H21.
User 10/11/2017 - 04:10
Can you write the formula for me so it will automaticall go to the cell beyond cell with word in it?
Excelchat Expert 10/11/2017 - 04:11
=countif(H2:H500,"Yes")
Excelchat Expert 10/11/2017 - 04:11
That is the formula you use and you paste that in H501.
User 10/11/2017 - 04:12
thank you, this will solve my problem. Excellent.
Excelchat Expert 10/11/2017 - 04:12
Perfect! it was my pleasure :)
Excelchat Expert 10/11/2017 - 04:13
Please be sure to rate your service today after ending the session.

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.