Indirect and Sumproduct functions?

**steveexcels** · 05-24-2012, 10:47 PM

Please help, I've been at this for a full day now.

There are three columns: A, B, and C. The data in A are number inputted manually. I want column B to sum up the numbers in column A. Here's the catch, I want the value in B to reset once data is entered in Column C. The data in column C will be entered sporadically.

From the hours spent searching i've managed to find someone with a somewhat similar problem. Only, I can't make heads or tails of the formula, and in any case, its not working for me: sum(indirect("p"&sumproduct(max(($p$8:p9=0)*row($p$8:p9)))+1:p9)))

What i've got thus far is: =if(not(isblank(c1)),a1, ... )

**benishiryo** · 05-24-2012, 10:59 PM

hi steveexcels, welcome to the forum. when u say Column B sums up the numberS, it sums every row of Column A? So if A1:A10 is filled, B1 to B10 = SUM(A1:A10)? and what does reset do? blank?

maybe u can upload a sample for us & show us wat u need to show

**steveexcels** · 05-24-2012, 11:08 PM

Thanks for the reply benishiryo!

Working on how to figure out how to upload.

But, yes, I think you've got the right idea. Column A will have a series of numbers from A1:...., I want column B to add the numbers from column A. So B1 would be A1. B2 would be A1+A2. etc. I think the formula for that would be =sum($A$1:A1).

The problem is this: everytime someone inputs a number in column C, I want the sum function in column B to start over again. For example say in C3 there is a number, I want B3 to then be A3, and not A1+A2+A3. This is the "reset" that I am talking about.

I've probably confused you more with that "explanation".

**steveexcels** · 05-24-2012, 11:20 PM

Picture 2.png

Here's a screenshot of what I am trying to accomplish. The middle column "B" was done manually. The "0" in B4 should actually be 144.4 (i.e. 144.4 + 0)

**benishiryo** · 05-24-2012, 11:41 PM

ahhh it isnt more confusing, but a screenshot is not very ideal haha. anyways, your explanation gave a clearer picture. but, are u expecting the cells below B3 to be reset too? means B4 is = SUM(A3:A4)? or is it still running from A1?

**steveexcels** · 05-24-2012, 11:53 PM

exactly. I would like B4 = sum (a3:a4). The problem is coming up with a formula that will accomplish this. I would like the B column to be reset everytime there is an entry in column C.

The following formula seemed to work for a similar situation: sum(indirect("p"&sumproduct(max(($p$8:p9=0)*row($p$8:p9)))+1:p9))). However, in that case the "trigger" to reset the sum formula seemed to be an entry of "0".

**benishiryo** · 05-25-2012, 10:54 AM

wow u really had me use up all of my brain cells. hope this works for u. tried on my end & worked

using the same eg where data starts from A1:A10, put this in B1:
=SUM(OFFSET($A$1,IF(MAX(ROW($C$1:C1)*($C$1:C1<>""))=0,MAX(ROW($C$1:C1)*($C$1:C1<>"")),MAX(ROW($C$1:C1)*($C$1:C1<>""))-1),0,IF(MAX(ROW($C$1:C1)*($C$1:C1<>""))=0,ROW(),ROW()-MAX(ROW($C$1:C1)*($C$1:C1<>""))+1)))

this is an array formula, so press CTRL + SHIFT + ENTER when in the formula

let me know if it works