Sum values in a row till blank cell

GLOJACK

New Member
Joined
Sep 16, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hello, hoping someone can help, there have been similar questions for a formula such as this, but I haven't seen it in row form. I am looking to achieve the # in column answer. The rows #'s for the date in which they appear, I only want to count in sequence from the start # i the last cell (9/14) to the last # before the blank cell. Please can anyone assist?

Row Labels
9/8/2020​
9/9/2020​
9/10/2020​
9/11/2020​
9/12/2020​
9/13/2020​
9/14/2020​
ANSWER
A
1​
0
B
1​
1​
2​
1​
1​
1​
2​
C
1​
1​
1​
1​
1​
1​
4​
D
1​
1​
1​
0​
E
1​
1​
1​
0​
F
1​
1​
1​
1​
G
1​
1​
1​
1​
1​
1​
3​
H
1​
1​
1​
I
1​
1​
1​
1​
1​
1​
1​
7​
J
1​
0​
K
1​
1​
1​
0​
L
1​
1​
1​
1​
2​
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

t0ny84

Board Regular
Joined
Jul 6, 2020
Messages
132
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
If I understand you correctly you want to add all figures in Row A from the column beginning with 9/8/2020 until 9/14/2020?
If that's correct then putting =COUNT(A1:D1) into the Answer column cell of Row A will add all numbers within Row A together.

Just swap A1:D1 to what the cell references are.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,384
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
+Fluff New.xlsm
ABCDEFGHI
1Row Labels08/09/202009/09/202010/09/202011/09/202012/09/202013/09/202014/09/2020ANSWER
2A10
3B1121112
4C1111114
5D1110
6E1110
7F1111
8G1111113
9H111
10I11111117
11J10
12K1110
13L11112
14
Main
Cell Formulas
RangeFormula
I2:I13I2=COLUMNS(A2:H2)-IFERROR(LOOKUP(2,1/(B2:H2=""),COLUMN(B2:H2)),1)
 

GLOJACK

New Member
Joined
Sep 16, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
If I understand you correctly you want to add all figures in Row A from the column beginning with 9/8/2020 until 9/14/2020?
If that's correct then putting =COUNT(A1:D1) into the Answer column cell of Row A will add all numbers within Row A together.

Just swap A1:D1 to what the cell references are.
Thank you Tony, I think you are saying to give a total count, but I just want it to count if there is a # in the most recent date
 

GLOJACK

New Member
Joined
Sep 16, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi & welcome to MrExcel.
How about...
Fluff thank you so much for your help , this worked perfectly !
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,384
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

GLOJACK

New Member
Joined
Sep 16, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Glad we could help & thanks for the feedback.
Please can you help me again. The data set is pulling from a pivot table, and when a new column is added the formula will only stop to last cell example below
1600749143519.png


So I changed the formula to pull more columns so it can recalculate max columns with data, but then it changes the formula to zero

1600749161713.png

Anyway to change it so it can recalculate to the last cell with data?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,384
Office Version
  1. 365
Platform
  1. Windows
Does your data go all the way to ZZ?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,384
Office Version
  1. 365
Platform
  1. Windows
In that case how would the formula know which cell to start counting in?
You wanted a formula that would return 0 if the last column was blank, which is what it does.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,494
Messages
5,602,004
Members
414,490
Latest member
Rip181

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top