Sum a certain amount of cells based on criteria

SaltyDalty

New Member
Joined
Jun 17, 2013
Messages
2
<o:p>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:
Days
Production (BBL)
1/1/13
10
1/2/13
20
1/3/13
15
1/4/13
1
1/5/13
1
1/6/13
10
1/7/13
30

<tbody>
</tbody>
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:p>
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.
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
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
ABCDEFGHI
1DaysProduction (BBL)CriteriaDaysTotalCorrect Result
201/01/2013101054765
302/01/20132010112145
403/01/20131515178220
504/01/20131
605/01/20131
706/01/201310
807/01/201310
908/01/201320
1009/01/201315
1110/01/201310
1211/01/201320
1312/01/201315
1413/01/20131
1514/01/201310
1615/01/201320
1716/01/201315
1817/01/201310
1918/01/201320
2019/01/201320
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

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
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
ABCDEFGHIJ
1DaysProduction (BBL)HelperCriteriaDaysTotalCorrect Result
201/01/20131011054765
302/01/201320210112145
403/01/201315315178220
504/01/201314
605/01/201315
706/01/2013106
807/01/2013107
908/01/2013208
1009/01/2013159
1110/01/20131010
1211/01/20132011
1312/01/20131512
1413/01/2013113
1514/01/20131014
1615/01/20132015
1716/01/20131516
1817/01/20131017
1918/01/20132018
2019/01/20132019
21
Sheet1


The formula in C2 needs to be copied down.

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

Excel Workbook
ABCDEFGHIJ
1DaysProduction (BBL)HelperCriteriaDaysTotalCorrect Result
201/01/20131011056565
302/01/201320210145145
403/01/201315315220220
706/01/2013104
807/01/2013105
908/01/2013206
1009/01/2013157
1110/01/2013108
1211/01/2013209
1312/01/20131510
1514/01/20131011
1615/01/20132012
1716/01/20131513
1817/01/20131014
1918/01/20132015
2019/01/20132016
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,456
Messages
5,596,234
Members
414,048
Latest member
wnied1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top