Sum Nth cell if next cell is empty

[ibobo]

Hello,

It is possible SUM green cell (F4, J4 ..) only if in orange cell (L4, P4...) is empty ?

orange cell is one cell away from left to the right


thank you ))

Excel file : https://www.catupload.com/download/d...0fc0e0569.html

 

[jorismoerings]

Hi,

see if this helps:

Book1
	A	B	C	D	E	F	G	H
1						1	2	3
2	#VALUE!	255,36		2		5		"1"=Oui
3	total	total
4	56,45	33,60	6	3,30	4	36	1,14	1
5	156,77	221,77	13	0,00	13	17	1,50
6	-70,00	-70,00	0	0,00	0	-5
7	-70,00	-70,00	0	0,00	0	-5
8
9
10
11
12					total	13,5951621
13
Sheet1

Cell Formulas
Range	Formula
F12	=SUMPRODUCT((MOD(COLUMN(H4:BH4),4)=0)*(H4:BH4=""),(F4:BF4))

 

[ibobo]

Hi,

see if this helps:

	A	B	C	D	E	F	G	H
1						1	2	3
2	#VALUE!	255,36		2		5		"1"=Oui
3	total	total
4	56,45	33,60	6	3,30	4	36	1,14	1
5	156,77	221,77	13	0,00	13	17	1,50
6	-70,00	-70,00	0	0,00	0	-5
7	-70,00	-70,00	0	0,00	0	-5
8
9
10
11
12					total	13,5951621
13

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula F12 =SUMPRODUCT((MOD(COLUMN(H4:BH4),4)=0)*(H4:BH4=""),(F4:BF4)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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

 

[ibobo]

soooory

it works but it take the negative result, may I use only positive result ? you see the -5 at the end ? without them

 

[jorismoerings]

something like this. This will only summarize the numbers if they are >0 and the orange cells is empty.

Book1
	A	B	C	D	E	F	G	H
1						1	2	3
2	#VALUE!	255,36		2		5		"1"=Oui
3	total	total
4	56,45	33,60	6	3,30	4	36	1,14	1
5	156,77	221,77	13	0,00	13	17	1,50
6	-70,00	-70,00	0	0,00	0	-5
7	-70,00	-70,00	0	0,00	0	-5
8
9
10
11
12						53,5951621
13
14
Sheet1

Cell Formulas
Range	Formula
F12	=SUMPRODUCT((MOD(COLUMN(H4:BH4),4)=0)*(H4:BH4="")*(F4:BF4>=0),(F4:BF4))