Question description:
This user has given permission to use the problem statement for this
blog.
IF SUM OF RANGE IS MORE THAN ZERO,THEN SUM
IF THE RANGE IS BLANK THEN BLANK
IF THE RANGE HAS ONLY ZERO,THEN ZERO
HOW SHALL I COMBINE THE ABOVE THREE IN A SINGLE CELL??
Solved by M. D. in 18 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
09/07/2018 - 01:28
Welcome to excelchat.
User
09/07/2018 - 01:28
mark entry
Excelchat Expert
09/07/2018 - 01:28
Hi, So you need help with a SUM, right?
User
09/07/2018 - 01:29
can i show what i need?
Excelchat Expert
09/07/2018 - 01:29
Sure, that will be a great help.
User
09/07/2018 - 01:30
i need to combine three scenarios into one formula
User
09/07/2018 - 01:31
1. if there arre values, summ it up
User
09/07/2018 - 01:31
2. if there is an entry "zero", display as zero
User
09/07/2018 - 01:31
3. if the ebtire range is blank, keep the result cell as blank
User
09/07/2018 - 01:31
pls help
Excelchat Expert
09/07/2018 - 01:32
I understand. I need you help help me to clear a few things.
Excelchat Expert
09/07/2018 - 01:32
First, in first row, it has a blank cell, what should be the result?
Excelchat Expert
09/07/2018 - 01:33
Do you understand my question?
User
09/07/2018 - 01:33
as there is a value in the row, not to consider the blank cell
Excelchat Expert
09/07/2018 - 01:33
Great. That is clear now.
User
09/07/2018 - 01:34
if the row is completely blank, the result cell should also display bank
User
09/07/2018 - 01:34
if there are entries - only "zero" then result cell shall display "zero"
Excelchat Expert
09/07/2018 - 01:34
Secondly, the range is always consistent or it may vary? For example, in the given example, there are five cells in the range.
Excelchat Expert
09/07/2018 - 01:35
I got it. "if there are entries - only "zero" then result cell shall display "zero""
User
09/07/2018 - 01:35
range is consistent with 10 horizontal cells
User
09/07/2018 - 01:35
yes
Excelchat Expert
09/07/2018 - 01:35
Thanks for the info. I have all I need.
Excelchat Expert
09/07/2018 - 01:35
Please give me a few minutes.
User
09/07/2018 - 01:36
need formula for 11th cell =result cell
Excelchat Expert
09/07/2018 - 01:36
Understood.
User
09/07/2018 - 01:36
cool
Excelchat Expert
09/07/2018 - 01:38
=IF(COUNTBLANK(A1:J1)=10,"",IF(SUM(A1:J1)=0,0,SUM(A1:J1)))
Excelchat Expert
09/07/2018 - 01:38
This is the required formula.
Excelchat Expert
09/07/2018 - 01:39
Please look at column K. I have applied it there.
User
09/07/2018 - 01:39
on eminute
Excelchat Expert
09/07/2018 - 01:39
Sure, take your time.
User
09/07/2018 - 01:41
CAN U MAKE "0" TO "ZERO"
Excelchat Expert
09/07/2018 - 01:41
Yeah
User
09/07/2018 - 01:41
when the entries are only zero
Excelchat Expert
09/07/2018 - 01:41
Done
User
09/07/2018 - 01:42
/how
Excelchat Expert
09/07/2018 - 01:42
=IF(COUNTBLANK(A1:J1)=10,"",IF(SUM(A1:J1)=0,"Zero",SUM(A1:J1)))
User
09/07/2018 - 01:42
am getting numeric 0
User
09/07/2018 - 01:42
let me try
User
09/07/2018 - 01:42
1 min
Excelchat Expert
09/07/2018 - 01:42
Sure. I'm here if you need me.
User
09/07/2018 - 01:43
WOW
User
09/07/2018 - 01:43
THAT WORKED
User
09/07/2018 - 01:43
THANK YOU SO MUCH
Excelchat Expert
09/07/2018 - 01:43
I'm glad to hear that.
Excelchat Expert
09/07/2018 - 01:43
My pleasure.
Excelchat Expert
09/07/2018 - 01:43
Please consider leaving a 5 star rating for me.
User
09/07/2018 - 01:43
how shall i give you the feedback 10/10??!!
User
09/07/2018 - 01:43
sure
Excelchat Expert
09/07/2018 - 01:44
Please click on the close button on your side to rate me.
User
09/07/2018 - 01:44
where is the option?
Excelchat Expert
09/07/2018 - 01:44
You need to click on the close/end button which is only on your side to end this chat.
Excelchat Expert
09/07/2018 - 01:44
After that you can rate me.
User
09/07/2018 - 01:44
sure
Excelchat Expert
09/07/2018 - 01:44
Thank you, have a great day!
User
09/07/2018 - 01:45
thank you; its amazing to live in this era!!
Excelchat Expert
09/07/2018 - 01:45
Yeah. :)
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.