What formula/function should I use?

[Shmeetbag]

What formula/function should I use?


I have tried Vlookup and IF functions but can’t seem to get this to work.
In column A1:A50 there will be a number imputed in random places. I need the sum of area B1:C50 between the numbers in column A returned in column D on the same row as the number in column A.

A1=3 B1=20 C1=5 D1=35
A2=””B2=10 C2=”” D2=””
A3=5 B3=””C3=10 D3=50
A4=”” B4=”” C4=30 D4=””
A5=”” B5=10 C5=”” D5=””
A6=”” B6=”” C6=”” D6=””
A7=8 B7=1 C7=””D7=1
A8=11 B8=12 C8=”” D8=12


3 is imputed in column A1 and returns 35 in D1 (20+10+5). 5 is imputed in A3 and returns 50 in D3 (10+30+10).

Thanks

 

[Shmeetbag]

The new formula got rid of the error, but it still returns the wrong #. Your original one worked fine, but when I put the formula below in the A column it messed everything up for some reason.

=IF(F3>1,A3,"")

 

[barry houdini]

Ok then

this should do it

=IF(ISNUMBER(A1),SUM(B1:INDEX(C1:C$100,IF(ISNA(MATCH(TRUE,INDEX(A2:A$100<>"",0),0)),ROWS(A2:A$100),MATCH(TRUE,INDEX(A2:A$100<>"",0),0)))),"")

 

[Shmeetbag]

Now that works great. I really appreciate it. You are the MAN!

 

[Domenic]

Here's another way...

D1, copied down:

=IF(A1<>"",SUM(OFFSET(B1:C$8,0,0,IF(ROWS(A1:A$8)>1,SMALL(IF(A1:A$8<>"",ROW(A1:A$8)-ROW(A1)+1),2)-1,1))),"")

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Adjust the range accordingly.

Hope this helps!