SUM data from a column if value from another column follows a specific value

[Peatawn]

Hi,

I'm trying devise a formula to sum the data from the "Seconds" column whenever a "1" follows a "4" from the "Mode" column. I don't want to sum where there are "4's" or "1's" following other "1's".

Here's an example (in this case the sum would be 59):
[TABLE="width: 50, align: left"]
<tbody>[TR]
[TD]Seconds[/TD]
[TD]Mode[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]55[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]
























To add to the complexity, the number of rows is variable from file to file.

One of the solutions I have right now is to implement a loop in my VBA to run through all the data record by record. But the point of this thread is to try and find another solution seeing as this has to be summed up in over 40 different daily-generated files that could contain somewhere between 200-300 records each.

Version: Excel 2007

Any ideas would be appreciated.

Thanks,

 

[konew1]

With your data header row in row 1 and the numeric data commencing in row 2

=SUMIFS(A2:A15,B1:B14,4,B2:B15,1)

 

[Peatawn]

Brilliant! It's why I didn't think of it before that frustrates me. Guess my head had seen enough of these tables I needed a brilliant mind to help me with it. Thanks a lot! I will be able to adjust this to make it applicable to tables of any row count.