Question description:
This user has given permission to use the problem statement for this
blog.
I need to convert the sum of 3 cells into a single number, ie if cell a and b = 200, 150 and cell c = 0, i only want cells with a value greater than zero to be counted until such a time that cell gets a value
Solved by V. L. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
15/12/2017 - 04:53
Welcome to excel got it pro.
Excelchat Expert
15/12/2017 - 04:54
Can you please elaborate this part "i only want cells with a value greater than zero to be counted until such a time that cell gets a value"
User
15/12/2017 - 04:55
basically i want to include that cell in the formula, but for it only to be counted if its value becomes greater than zero
User
15/12/2017 - 04:56
i have attached an example here
Excelchat Expert
15/12/2017 - 04:56
Some examples would be great.
Excelchat Expert
15/12/2017 - 04:57
okay, so if you use SUM function then it will automatically omit the 0 valued cells.
Excelchat Expert
15/12/2017 - 04:58
Or is it you want to know how many properties are worth more than 0?
User
15/12/2017 - 04:58
cells 1 c and d are populated giving a total of 600 if i divide that by the number of properties (4) will give an average of 150, but there are only 3 cells populated so it needs to be divisible by 3
User
15/12/2017 - 04:58
properties yes worth more than 0
Excelchat Expert
15/12/2017 - 04:58
okay, understood.
User
15/12/2017 - 04:58
thanks
Excelchat Expert
15/12/2017 - 04:59
So, basically you want to get average, right?
User
15/12/2017 - 04:59
not actually averages but a single number (quantity) not totals
Excelchat Expert
15/12/2017 - 04:59
okay. got it.
Excelchat Expert
15/12/2017 - 05:00
=countif(A3:D3,">0")
Excelchat Expert
15/12/2017 - 05:00
here is the formula, it will give you the count of non zero cells.
Excelchat Expert
15/12/2017 - 05:01
A3:D3 is the range and ">0" means greater than 0 cells.
Excelchat Expert
15/12/2017 - 05:01
Please apply it on your sheet and let me know.
User
15/12/2017 - 05:02
do u type =a3:d3">0"
Excelchat Expert
15/12/2017 - 05:03
no this is the whole formula =countif(A3:D3,">0")
User
15/12/2017 - 05:03
thanks
Excelchat Expert
15/12/2017 - 05:03
anything else?
User
15/12/2017 - 05:03
brilliant, thanks for that
Excelchat Expert
15/12/2017 - 05:03
IF not please close the session.
Excelchat Expert
15/12/2017 - 05:04
Thank you for visiting us. Have a great day!
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.