Sum Nth cell if next cell is empty

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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
830
RoryA
RoryA
G
  • Question Question
'Storing' and summing data before it is overwritten.
Replies
1
Views
198
gwoolley0302
G
R
  • Question Question
Help to sum all cells above until blank cell
Replies
3
Views
677
RemyMoo
R
D
  • Solved
Counting Consecutive Blanks
Replies
9
Views
644
felixstraube
F
Wellness
  • Solved
SUM cells in a column >0 based on the month and year of another column
Replies
5
Views
587
awoohaw
awoohaw

Forum statistics

Threads
1,223,894
Messages
6,175,254
Members
452,623
Latest member
Techenthusiast

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