How to Sum Only Positive Numbers in Columns not Next To Each Other

[SanFelippo]

I originally thought this was going to be a simple formula, but I realized that the usual SUMIF and/or SUMIFs formulas doesn't work for this situation. And no, I unfortunately can't moved columns around to make this easier.

So here is an example

A	B	C	D	E	F	G	H	I
1	4	-5	6	7	8	3	4	-4
-2	3	6	4	3	2	4	6	7

So in this example, I need a formula that will sum together the Row values that are in Columns C,F,and I only if they are positive values

So the output in some other cells for Row 1 and 2 would be as follows:

Row 1 Output (put it in Cell J1): 8
Row 2 Output (put in Cell J2): 15

Does that make sense? I do have a formula that does work, but it involved coding every single situation and the corresponding sum (Combination and Permutations stuff). In my actual workbook, it looks like this:

=IF(AND(P4>=0,X4>=0,AB4>=0),SUM(P4,X4,AB4),IF(AND(P4>=0,X4>=0,AB4<0),SUM(P4,X4),IF(AND(P4>=0,AB4>=0,X4<0),SUM(P4,AB4),IF(AND(X4>=0,AB4>=0,P4<0),SUM(X4,AB4),IF(AND(P4<0,X4<0,AB4>=0),AB4,IF(AND(P4<0,X4>=0,AB4<0),X4,IF(AND(P4>=0,X4<0,AB4<0),P4,IF(AND(P4<0,X4<0,AB4<0), 0,""))))))))

This works but it is ugly. I figure there has got to be an easier way to do this. Any thoughts?

 

[Eric W]

How about:

=SUM(IF(C1>0,C1),IF(F1>0,F1),IF(I1>0,I1))

If you have more cells to consider, there are ways to not have to explicitly list the cells, if they have a pattern.

 

[Scott Huish]

Not sure I understand your sample, but based on your formula, does this work for you?

=SUM(MAX(P4,0),MAX(X4,0),MAX(AB4,0))

 

[DanteAmor]

If you are going to add more columns and the sum is every three columns, you can try the following:

Dante Amor
	M	N	O	P	Q	R	S	T	U	V
1
2
3
4	1	4	-5	6	7	8	3	4	-4	8
5	-2	3	6	4	3	2	4	6	7	15
S1

Cell Formulas
Range	Formula
V4:V5	V4	=SUMPRODUCT((MOD(COLUMN($M$1:$U$1),3)=0)*(M4:U4>0)*(M4:U4))

 

[KRice]

Another approach to consider:
=SUMPRODUCT(C3*(C3>0)+F3*(F3>0)+I3*(I3>0))

 

[SanFelippo]

How about:

=SUM(IF(C1>0,C1),IF(F1>0,F1),IF(I1>0,I1))

If you have more cells to consider, there are ways to not have to explicitly list the cells, if they have a pattern.

I am actually really disappointed in myself. This was the easiest work around ever and I should have seen it immediately.

This works perfectly. Thanks a bunch everyone.

 

[DanteAmor]

For 3 cells, it could be:

=IF(C1>0,C1)+IF(F1>0,F1)+IF(I1>0,I1)

 

[Eric W]

I'm an expert in overlooking the obvious myself! Sometimes another set of eyes can really make a difference. Glad we could help.