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.
