Simifs between a range

[GordonKy42]

Hey Guys,

Hoping someone can help been going round in circles trying to figure this out. I have attached a test sheet to help explain

Looking to have a formula that can do the following

In Cells in G:G If the Corresponding Cell in F:F = "Yes" then sumif if the Cell in B:B is in the Range E:E it should sum the Values in C:C but only between the starting 0 in column A:A and the ending column where that cell in G:G lies between in A:A.

For EG: in cell G155 the 1st '0' in column A:A is in A141 and the last is in A177
so as F155 = "Yes" the formula should sum the values C170:C173 as the value in B155 matches those in E170:E173 it should = 1334.205

Likewise the Value in G2 should = 20,290.18
G8 should = 29,793.04

Any and all help greatly appreciated.

Many thanks in advance

[davsth]

I am not sure of the relevance of the between the 0s in the explaination, but does the below work for g2

=IF(F2="yes",SUMIF(E:E,B2,C:C),"")

[GordonKy42]

Hey,

Thanks for having a look, no I am afraid not it needs to only sum between the 0's in column A:A this is the bit I have been having issues with. Reason is that the main file I have is for my order book an I am working on that has over 45k lines like this with other data. The Assembly cost is not an accurate representation of the cost right now I need it to sum as I described above. Reason I need it like above is that I have a few places where the values further down the list will be the same but for another order so they will give a false value for all of these.

Hope that makes sense

thanks

[davsth]

you need to explain the 0s is it to sum from the line of the yes to the line with the next 0?

if you are using the range to the 0 in column A below the row in question the below might work
=IF(F2="yes",SUMIF(OFFSET(E3,0,0,MATCH(0,A3:$A$177,0),1),B2,OFFSET(C3,0,0,MATCH(0,A3:$A$177,0),1)),"")