Average Multiple Rows in Multiple Columns Based off Multiple Criteria Points

Good Morning,

This is my first post, but have already learned so much from going through other posts on this forum. I always considered myself pretty handy when it came to excel, that is until i came to this forum and saw even more in depth coding. Thank you so much for all that you have already shown me and helped me with. But i have come upon one that i either cant wrap my mind around, or my mind is so fried from trying so many different formulas and not being able to come up with a solution.

Now for the problem:

I have a report that i pull and typically go through it manually each time and get the data i need from. This downloaded sheet "Motor KPI" has thousands of data points ranging from Column A-DD & can have anywhere to 100-3000+ Rows. I usually filter the top row and then just select the certain Operators and Rigs and then find the data i need and average it to get what i need.

I am wanting to create a workbook, that i can copy and paste the "Motor KPI" Sheet into and then in the "Avg ROP" sheet i type in the specific Operator & Rig in Columns A-B, then it auto filters the multiple sections based off the typed in Operator and Rig and filters through the "Phases" and then three additional sections based off the phase type , thus averaging each section from the Motor KPI sheet

Out of all the columns only 6 are needed to be filtered on Motor KPI Sheet:
Column A (Operator)
Column B (Rig)
Column Q (Rotate ROP)
Column R (Slide ROP)
Column S (Avg ROP)
Column CS (Phases) - Multiple Phases that Columns Q,R&S based off of. ( SUR: Surface, INT: Intermediate, INT2: Intermediate 2, INT2: Intermediate 2 / CRV: CURVE, CRV/LAT: CURVE/LATERAL, LAT: Lateral)

Then on Avg ROP Sheet
Column A (Operator) - Manually type in otherwise leave all data following blank if nothing typed in
Column B (Rig) - Manually type in otherwise leave all data blank if nothing typed in
Column D1 (Selected Phase)- Find all the rows in "Motor KPI" sheet that have SUR: Surface (Column CS) and then find the multiple selected data from columns Q, R, & S under selected phase and average the multiple rows of selected data in D3:F3
Column H1:X1 - Perform the same as above for each selected phase and sub category.
Column AB - Average all rows for selected operator/rig.

Im ok with using formulas, vba coding, or macros.

In D4

=IFERROR(AVERAGEIFS('Motor KPI'!Q$2:Q$22,'Motor KPI'!$A$2:$A$22,$A4,'Motor KPI'!$B$2:$B$22,$B4,'Motor KPI'!$CS$2:$CS$22,$D$1),"")

Copy across to F and down

Same formula for other phase: change $D$1 to appropriate "phase" in row 1

John, thank you very much for such a quick reply and solution. I have been banging my head against the wall for a couple days not way overthinking the needed formula. Funny enough, i even had very similar to the one you provided written down, just kept over looking it. I definitely owe you the beverage of your choice if you ever find you way to Texas. Thank you again.

You're welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Yes sir i already did both of those. Thank you again.

John,

Actually just realized something. I need it to look for the last row over all in all need sections in the Motor KPI tab, When i pull a fresh report it can have either 25 lines, or over a couple thousand lines of data. I will then copy and paste that report into an empty tab.

Easiest way is to change the range (from 22) to your likely maximum e.g. 2000

Added examples of new criteria: check columns P and X

Formula:

Please Login or Register  to view this content.

This example works to an extent. I am wanting it to instead of looking for the exact copy of the phrase in P1, it look for the key words "Intermediate / Curve." So that if it does have the INT2: Intermediate 2 in there, it will over look the # 2 and still pull from that row since it has still has intermediate / curve in it.

There is a little over 80 different combinations and variants of how the sequence can look, so was wanting it to look for the keyword in the sequence, instead of the exact match.

The key words i am wanting to use in the top row for each section are:

Surface
Intermediate
Intermediate / Curve
Intermediate / Curve/ Lateral
Curve
Curve / Lateral
Lateral

Given the number of combinations it could be very complex

These are found under LAT: LATERAL

INT: INTERMEDIATE / CRV: CURVE / LAT: LATERAL
INT2: INTERMEDIATE 2 / CRV: CURVE / LAT: LATERAL / SUR: SURFACE
LAT: LATERAL
LAT: LATERAL
LAT: LATERAL / PILOT: PILOT
LAT: LATERAL
SUR: SURFACE / LAT: LATERAL

BUT the bold are phases in their own right

Can you remove SOLVED from this thread OR start a new one.

Please post an updated file with some example expected results

The SUMPRODUCT will work with the new "phases": however the matches need to be exact i.e extraneous blanks will cause a mismatch.

Very complex indeed. We are dependent upon the other people putting in the data properly, but obviously they do not all the time, but the data is still important and needed. Just makes it more tedious to go through. Either way, i have attached an updated sheet and Starting on row 20, displayed it how i wanted it to look. Beginning on A35:A41 i have the key words that are in row 20 color coded. C34:C113 I have most of the different types of sequence that it could look like. I colored which ones would be needed to match with the keyword in A35:A41. If the cell under C34 Remains uncolored, it would be to difficult and require a manual data analysis to determine the proper place and/or just forget it and it wont be taken into account and passed over.

A35:41 - Color Coded Keywords
C34:113 - Color Coded Sequences that match with Keywords in A35:A41
A20:AH31 - How i am wanting it to look (More than likely will Copy and transpose to make it vertical to give it a better looking formatted visual like in J35:U68)