Sum a certain amount of cells based on criteria

[SaltyDalty]

<o>Hey everyone! So I am having some issues creating a formula that will add (sum) the first 30 cells, if they meet the criteria, and then stop after 30 cells, that have meet the criteria, have been added (summed). This is what I am trying to do; I am currently working on a daily production chart for the amount of oil each oil rig produces. I have the daily production data but I need to create a table that shows the amount of oil produced in 30 day, 60 day, 90 day, and so on. However, the hard part is that we only want to count the days that the oil rig was producing oil and exclude the days that it was broken/down/not producing. Here is an example of what the chart looks like:[TABLE="width: 500"]
<tbody>[TR]
[TD]Days
[/TD]
[TD]Production (BBL)
[/TD]
[/TR]
[TR]
[TD]1/1/13
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]1/2/13
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]1/3/13
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD]1/4/13
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1/5/13
[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1/6/13
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]1/7/13
[/TD]
[TD]30
[/TD]
[/TR]
</tbody>[/TABLE]
So if this is the data I wanted to create a formula that would sum only if the production was greater then or equal to 10 BBL (barrels) a day and only add the first, in this case, 3 cells (days) that meet the criteria. I know I could do the =sum(B2:B4) and get my answer but the problem is that we have 16 oil rigs and they have over 1095 days (rows) of data for each one. No only that but I need show how much oil was produced (as long as it was no broken) all the way up to 720 days based on 90 day increments. Also, not every oil rig was broken at the same time so I cant just do a sum of the first 30 days and copy and paste that formula into each of the other 16 oil rigs. </o> so the Sosoasdf
So I was trying to create a formula that would Sum the first 30 cells (days) that had a daily production of 10 BBLs or more and would stop after it had added 30 values. I am sorry if this is confusing at all. Its really had to explain in words but if you need clarification just let me know. Thank you so much!!!!! This will make my life a million times easier.

 

[Akashwani]

Hi and welcome to MrExcel.

I don't have the complete solution for you, sorry.

I'm hoping that someone reads this and can add the required IF to meet the criteria of >= 10

The following formula will SUM the nth number of cells based on the the number of rows given in column E....

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Days	Production (BBL)		Criteria	Days	Total		Correct Result
2	01/01/2013	10		10	5	47		65
3	02/01/2013	20			10	112		145
4	03/01/2013	15			15	178		220
5	04/01/2013	1
6	05/01/2013	1
7	06/01/2013	10
8	07/01/2013	10
9	08/01/2013	20
10	09/01/2013	15
11	10/01/2013	10
12	11/01/2013	20
13	12/01/2013	15
14	13/01/2013	1
15	14/01/2013	10
16	15/01/2013	20
17	16/01/2013	15
18	17/01/2013	10
19	18/01/2013	20
20	19/01/2013	20
21
Sheet1

I'm struggling to find a way to add the IF the cell value in column B is >= 10 then SUM the first Nth cells that match.

Fingers crossed.

Ak

 

[Akashwani]

Hi,

After rethinking my approach to this problem and doing a quck searchon here for SUBTOTAL and IF, I have come up with the following solution IF you are prepared to use a "Helper" column and Filter....

Unfiltered data...

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Days	Production (BBL)	Helper		Criteria	Days	Total		Correct Result
2	01/01/2013	10	1		10	5	47		65
3	02/01/2013	20	2			10	112		145
4	03/01/2013	15	3			15	178		220
5	04/01/2013	1	4
6	05/01/2013	1	5
7	06/01/2013	10	6
8	07/01/2013	10	7
9	08/01/2013	20	8
10	09/01/2013	15	9
11	10/01/2013	10	10
12	11/01/2013	20	11
13	12/01/2013	15	12
14	13/01/2013	1	13
15	14/01/2013	10	14
16	15/01/2013	20	15
17	16/01/2013	15	16
18	17/01/2013	10	17
19	18/01/2013	20	18
20	19/01/2013	20	19
21
Sheet1

The formula in C2 needs to be copied down.

Filtered data using Number Filters Greater than or Equal to 10....

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Days	Production (BBL)	Helper		Criteria	Days	Total		Correct Result
2	01/01/2013	10	1		10	5	65		65
3	02/01/2013	20	2			10	145		145
4	03/01/2013	15	3			15	220		220
7	06/01/2013	10	4
8	07/01/2013	10	5
9	08/01/2013	20	6
10	09/01/2013	15	7
11	10/01/2013	10	8
12	11/01/2013	20	9
13	12/01/2013	15	10
15	14/01/2013	10	11
16	15/01/2013	20	12
17	16/01/2013	15	13
18	17/01/2013	10	14
19	18/01/2013	20	15
20	19/01/2013	20	16
21
Sheet1

I found the SUMPRODUCT/SUBTOTAL formula here....

http://www.mrexcel.com/forum/excel-questions/86211-subtotal-if.html#post422699

You can change the values in column F to 30, 60 90 days.

I hope that helps.

Ak

 

[SaltyDalty]

Thank you so much! This will help out a lot! You honestly made my day!

 

[Akashwani]

Hi,

I'm pleased it helped and thanks for your feedback.

Ak