Question description:
This user has given permission to use the problem statement for this
blog.
I have a large spreadsheet and I need to sum groups of numbers in one column in the empty cell right below the numbers. There are thousands of instances with a spreadsheet that has over 200K rows
Solved by B. H. in 43 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
06/07/2018 - 04:20
Hello, I understand that you need to sum all the all your numbers at the bottom of the list, right?
User
06/07/2018 - 04:21
There are several subsets of numbers in one column. I need a total at the bottom of each subset of numbers.
Excelchat Expert
06/07/2018 - 04:22
I see. Can you give me an example of how your data looks like using the document to the right?
Excelchat Expert
06/07/2018 - 04:22
Do you want the sum where you wrote the word sum?
User
06/07/2018 - 04:22
yes
Excelchat Expert
06/07/2018 - 04:23
So there's an empty space on top of it?
User
06/07/2018 - 04:23
where I have the word 'sum' is an empty cell
Excelchat Expert
06/07/2018 - 04:24
How about the cell above it? If the cell with SUM is supposed to be empty, does that mean that the cell above it is empty as well, meaning 2 empty cells?
User
06/07/2018 - 04:24
yes. that is correct
Excelchat Expert
06/07/2018 - 04:24
Okay, this one is a bit tricky and it may need VBA/Macro to solve but let me try something first.
Excelchat Expert
06/07/2018 - 04:24
Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question. We also currently do not support VBA/Macro solutions.
Excelchat Expert
06/07/2018 - 04:25
The number of cells per group varies, right?
User
06/07/2018 - 04:26
yes. that is correct
Excelchat Expert
06/07/2018 - 04:29
Is there a way to remove the extra empty cell?
Excelchat Expert
06/07/2018 - 04:30
I'm asking because that's what makes this impossible with formulas alone.
Excelchat Expert
06/07/2018 - 04:31
Are you still with me?
Excelchat Expert
06/07/2018 - 04:34
Are you still there?
Excelchat Expert
06/07/2018 - 04:34
I have a solution on how to do this but it will compose of multiple steps and it would be best if you can reply to me so I can start the explanation.
User
06/07/2018 - 04:35
ok
Excelchat Expert
06/07/2018 - 04:35
Let me write the steps.
Excelchat Expert
06/07/2018 - 04:36
Please extend the session once prompted.
Excelchat Expert
06/07/2018 - 04:36
If the extra empty cell can't be removed then there'll be plenty of extra steps so please follow the steps carefully.
Excelchat Expert
06/07/2018 - 04:38
Is column F the column where your numbers are originally placed?
Excelchat Expert
06/07/2018 - 04:38
I'm only asking so I can reference the column properly in the instructions.
User
06/07/2018 - 04:38
yes
Excelchat Expert
06/07/2018 - 04:38
Does your numbers start in row 1 or do you have a header?
User
06/07/2018 - 04:39
header
Excelchat Expert
06/07/2018 - 04:39
Do you know how to filter a column?
User
06/07/2018 - 04:39
no
Excelchat Expert
06/07/2018 - 04:39
Okay, I'm going to walk you through this then. This will involve quite a few steps so please bear with me.
Excelchat Expert
06/07/2018 - 04:39
Do you have anything in column G?
User
06/07/2018 - 04:40
yes
Excelchat Expert
06/07/2018 - 04:40
What is the column that is not used?
Excelchat Expert
06/07/2018 - 04:40
I meant, which is the first unused column after F?
User
06/07/2018 - 04:40
i
Excelchat Expert
06/07/2018 - 04:42
Place this formula in I1
Excelchat Expert
06/07/2018 - 04:42
=IF(AND(F1="",F2=""),"FILL","")
Excelchat Expert
06/07/2018 - 04:42
Let me know once you've done it.
Excelchat Expert
06/07/2018 - 04:43
Hello, we have a very limited amount of time to do this. I'd really appreciate it if we act now or we might not be able to solve this in time.
User
06/07/2018 - 04:44
unfortunately have to go now . thank you
Excelchat Expert
06/07/2018 - 04:44
Okay, let me at least teach you how to do it.
Excelchat Expert
06/07/2018 - 04:44
Then you can try it once you have the time.
Excelchat Expert
06/07/2018 - 04:54
The steps to accomplish what you need to do is in cell A1 of the document preview.
Excelchat Expert
06/07/2018 - 04:54
If you can find a way to remove the extra empty cell in your data then the steps in B1 would suffice.
Excelchat Expert
06/07/2018 - 04:56
Hi, I have not received a reply from you for quite some time. I'm afraid the session will end automatically soon. I sincerely hope we were able to address your concern.
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.