I am trying to do simple formula =AS2+AP3 in cell AS3 for the entire row AS. So in cell AS4 it would be =AS3+AP4. I can do it simply with copy and paste. But it doesn't work with arrayformula. Is there a way around it?
Add a comment
|
1 Answer
I think you want a running total of the numbers in column AP, but starting with whatever number is in AS2. You can do this sort of thing with a SUMIF which adds up all the numbers up to the current row:
=ArrayFormula(if(AP3:AP="","",sumif(row(AP3:AP),"<="&row(AP3:AP),AP3:AP)+AS2))
9 Comments
Nacho Libre
That worked perfectly. But is that the easiest way to do that sort of thing? Seems complicated. THANK YOU.
Tom Sharpe
This is the only method I know of that works, because an array formula can't use a previous cell's result in the way that a normal formula copied and pasted can.
Nacho Libre
Thank you very much Tom. Have a great day.
Nacho Libre
Hello Tom. I'm sorry to bother you, but I see that you are very smart and knowledgeable guy. And your answers work and are right on point. I have 2 questions that can't get answered properly. Here are the links [link]stackoverflow.com/questions/54777684/averageifs-div-0-solution and [link]stackoverflow.com/questions/54774699/array-formula-and-max .If you have time. I appreciate any help and THANK YOU.
Tom Sharpe
Have had a quick look at the other question, short answer might be to use SUMIFS(...)/COUNTIFS(...), testing for COUNTIFS(...) being zero instead of using AVERAGEIFS.
|