Adding non sequential cells in a column

[Nanaia]

Is there a way to add non sequential cells in a column without having to select every cell? If possible I would like to have a more concise formula to replace

=IFERROR(IF(L102<>"",IF(AND(V29-P30-P33-P36-P39-P42-P45-P48-P51-P54-P57-P60-P63-P66-P69-P72-P75-P78-P81-P84-P87-P90-P93-P96-P99>0,V29-P30-P33-P36-P39-P42-P45-P48-P51-P54-P57-P60-P63-P66-P69-P72-P75-P78-P81-P84-P87-P90-P93-P96-P99<=45),V29-P30-P33-P36-P39-P42-P45-P48-P51-P54-P57-P60-P63-P66-P69-P72-P75-P78-P81-P84-P87-P90-P93-P96-P99,IF(V29-P30-P33-P36-P39-P42-P45-P48-P51-P54-P57-P60-P63-P66-P69-P72-P75-P78-P81-P84-P87-P90-P93-P96-P99>45,45,0)),""),"")

If anyone can help it would be appreciated.

 

[jtakw]

Hi,

Do the values in Column P (e.g. P30, P33, etc.) have any identifiers in another Column (e.g. date, text, etc.) ?

 

[Nanaia]

I'm not sure what you mean by identifier. The non sequential cells are a numerical value that is the result of a formula. Whole numbers, no text and 'general' formatting.

 

[Joe4]

You seem to be subtracting every third row (from P30 - P99). So you may be able to incorporate that into a formula using MOD.

For example, this whole section:

=V29-P30-P33-P36-P39-P42-P45-P48-P51-P54-P57-P60-P63-P66-P69-P72-P75-P78-P81-P84-P87-P90-P93-P96-P99

could be replaced with this:

=V29-SUMPRODUCT(--(MOD(ROW(P30:P99),3)=0),P30:P99)

So you should be able to re-use that logic for the other sections of your formula.

 

[jtakw]

I'm not sure what you mean by identifier. The non sequential cells are a numerical value that is the result of a formula. Whole numbers, no text and 'general' formatting.

What I mean is this:

Book3.xlsx
	O	P
1	Apple	1
2	Orange	2
3	Pear	3
4	Apple	4
5	Orange	5
6	Pear	6
7	Apple	7
8	Orange	8
9	Pear	9
10	Apple	10
Sheet980

 

[Joe4]

Since you also seem to be doing the same calculation over-and-over again, I think you may be able to use the new LET function.
I have seen them done, but unfortunately, I have not really played around with them yet.

 

[Joe4]

OK, I just messed around with the LET function for the first time (it was easier than I thought), and I think your whole formula can be replaced with this:

=LET(temp,V29-SUMPRODUCT(--(MOD(ROW(P30:P99),3)=0),P30:P99),IFERROR(IF(L102<>"",IF(AND(temp>0,temp<=45),temp,IF(temp>45,45,0)),""),""))

 

[Nanaia]

@jtakw Thank you. That seems to work.
@Joe4 Thank you also. Your formula seems to also work. I will test both of them to determine if one suits the situation better.
What I'm doing is breaking down a user entered number into segments of not more than 45. So every cell listed is the previous segment of 45 subtracted from the total until the result is zero. It's a pain to do it with pen and paper or calculator so thought this would help my coworkers.
Thank you for your help.

 

[jtakw]

What I mean is this:

Book3.xlsx
	O	P
1	Apple	1
2	Orange	2
3	Pear	3
4	Apple	4
5	Orange	5
6	Pear	6
7	Apple	7
8	Orange	8
9	Pear	9
10	Apple	10
Sheet980

@jtakw Thank you. That seems to work.

Then you can just replace the Entire P column cells in your formula with a simple SUMIF