Sum Nth cell if next cell is empty

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
Hi,

see if this helps:


Book1
ABCDEFGH
1123
2#VALUE!255,3625"1"=Oui
3totaltotal
456,4533,6063,304361,141
5156,77221,77130,0013171,50
6-70,00-70,0000,000-5
7-70,00-70,0000,000-5
8
9
10
11
12total13,5951621
13
Sheet1
Cell Formulas
RangeFormula
F12=SUMPRODUCT((MOD(COLUMN(H4:BH4),4)=0)*(H4:BH4=""),(F4:BF4))
 
Upvote 0
jorismoerings said:
Hi,

see if this helps:

ABCDEFGH
totaltotal
total

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ED7D31]#ED7D31[/URL] , align: right"]3[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]#VALUE![/TD]
[TD="align: right"]255,36[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ED7D31]#ED7D31[/URL] "]"1"=Oui [/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ED7D31]#ED7D31[/URL] , align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]56,45[/TD]
[TD="align: right"]33,60[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]3,30[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]36[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]1,14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ED7D31]#ED7D31[/URL] , align: right"]1[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]156,77[/TD]
[TD="align: right"]221,77[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]0,00[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]1,50[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ED7D31]#ED7D31[/URL] , align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]-70,00[/TD]
[TD="align: right"]-70,00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0,00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ED7D31]#ED7D31[/URL] , align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]-70,00[/TD]
[TD="align: right"]-70,00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0,00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ED7D31]#ED7D31[/URL] , align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]13,5951621[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F12[/TH]
[TD="align: left"]=SUMPRODUCT((MOD(COLUMN(H4:BH4),4)=0)*(H4:BH4=""),(F4:BF4))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
Thanks you Jorismoerings

unfortunately the result should be 32

on the line 4 there is three cell one with F4 >36, one J4 >9 and one N4 > 23. The first cell F4 has in H4 "1" so the orange cell is not empty. Then the J4 has nothing in the cell L4 (orange) and the last one N4 has also nothing in orange cell P4. So the sum should work only on J4 and N4 (9+23) = 32

sorry if I explain my self wrong, I work on this since three day and don't sleep much :laugh:
 
Upvote 0
soooory

it works but it take the negative result, may I use only positive result ? you see the -5 at the end ? without them
 
Upvote 0
something like this. This will only summarize the numbers if they are >0 and the orange cells is empty.


Book1
ABCDEFGH
1123
2#VALUE!255,3625"1"=Oui
3totaltotal
456,4533,6063,304361,141
5156,77221,77130,0013171,50
6-70,00-70,0000,000-5
7-70,00-70,0000,000-5
8
9
10
11
1253,5951621
13
14
Sheet1
Cell Formulas
RangeFormula
F12=SUMPRODUCT((MOD(COLUMN(H4:BH4),4)=0)*(H4:BH4="")*(F4:BF4>=0),(F4:BF4))
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Conditional Formatting.. The bane of my Excel Existence
Replies
2
Views
861
RoryA
RoryA
G
  • Question Question
'Storing' and summing data before it is overwritten.
Replies
1
Views
215
gwoolley0302
G
R
  • Question Question
Help to sum all cells above until blank cell
Replies
3
Views
695
RemyMoo
R
D
  • Solved
Counting Consecutive Blanks
Replies
9
Views
708
felixstraube
F
B
  • Question Question
Sum child cells and allow reference to higher cell within Table
Replies
7
Views
157
babyjwhale
B

Forum statistics

Threads
1,224,823
Messages
6,181,182
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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
Back
Top