AVERAGEIF with dynamic multiple criteria on same range

[rahmaddanis]

Hello,
I'm trying to use AverageIF to find the average of my parts weight.
The criterias will apply to the same criteria_range but the number of criteria itself will vary depending on user input.
What is the best way to do this?

[/URL][/IMG]

 

[Learning To Excel]

Never mind, I see what you mean. Just a moment and I'll post a new idea.

 

[Learning To Excel]

Here is one option for you, just place your codes in row 1 for this function to work. Alternatively you could keep your data where it is and replace the HLOOKUPS with INDEX-MATCH, but I think it's easier to just move your codes section.

=AVERAGE(HLOOKUP(M3,B1:J2,2),HLOOKUP(M4,B1:J2,2),HLOOKUP(M5,B1:J2,2))

 

[Learning To Excel]

If you need to allow for users to put less code criteria in then your formula calls for you could use this.

=SUM(IFERROR(HLOOKUP(M3,B1:J2,2),0),IFERROR(HLOOKUP(M4,B1:J2,2),0),IFERROR(HLOOKUP(M5,B1:J2,2),0))/COUNTA(M3:M5)

 

[rahmaddanis]

Hello,
from my understanding, I need to move the "Code" row above the "Weight" in the order to allow the formula works.
Can we retain the structure to make the formula works.

Another question, below is what I'm doing with the averageif on another sheet trying to get the same result.
=AVERAGE(IF((INDIRECT("'"&D8&"'!"&"B1:B76")=F7)*(INDIRECT("'"&D8&"'!"&"C1:C76")=Code_Range),INDIRECT("'"&D8&"'!"&"F1:F76")))

Code_Range is a dynamic range. The formula seems to work when the code_range consist 1 member only but fail when the member > 1. How to make it works with member >1.

B1:B76 is a range which will be filtered by 1 criteria.
C1:C76 is a range which will be filtered by multiple criteria in Code_Range

Thanks.

 

[bschwartz]

I don't quite understand what you're averaging since since all of your codes seem to have only one value, but maybe something like this can work for you?

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2	Weight	4.2474	4.2472	4.2889	4.2889	4.0929	4.2472	4.1203	4.2615	4.2894			Criteria	Avg. Weight
3	Code	BLC	BRC	CRB	FLM	NTL	REC	REF	SOR	SUT			BLC	4.2474
4													BRC	4.2472
5													REC	4.2472
Sheet1

Cell Formulas
Range	Formula
N3	=AVERAGEIF($B$3:$J$3,M3,$B$2:$J$2)

 

[rahmaddanis]

Ben, I'm trying to apply multiple criteria on same range, not one by one criteria.
My real data structure do not allow me to do that without pain.

 

[Learning To Excel]

(1) If by dynamic you mean that the data will increase in number but the same 8 codes will always be used then a single formula works.
The following function is the same as before but allows you to keep the data where it is, and it accounts for all 8 data types.

=SUM(IFERROR(INDEX(B2:J2,,MATCH(M3,B3:J3,0)),0),IFERROR(INDEX(B2:J2,,MATCH(M4,B3:J3,0)),0),IFERROR(INDEX(B2:J2,,MATCH(M5,B3:J3,0)),0),IFERROR(INDEX(B2:J2,,MATCH(M6,B3:J3,0)),0),IFERROR(INDEX(B2:J2,,MATCH(M7,B3:J3,0)),0),IFERROR(INDEX(B2:J2,,MATCH(M8,B3:J3,0)),0),IFERROR(INDEX(B2:J2,,MATCH(M9,B3:J3,0)),0))/COUNTA(M3:M100)

(2) If the data increases with many new codes and you don't want to be adding a "IFERROR(INDEX(B2:J2,,MATCH(M3,B3:J3,0)),0)" for each new possible code then I think a macro is the best solution.
The following is a code that will allow for any number of new codes and an increase in the data set. I took the liberty of assuming that the data continues on to the right of the existing data and so I moved your output range exactly 4 rows down.

Sub AverageFinder()
Dim CriteriaCounter As Integer
Dim RangeCounter As Integer
Dim RunningTotal As Double
Dim RunningCount As Integer
Dim i As Integer
Dim j As Integer


RunningTotal = 0
RunningCount = 0
CriteriaCounter = Application.WorksheetFunction.CountA(Range("M7:M100"))
RangeCounter = Application.WorksheetFunction.CountA(Range("B3:HH3"))


    For i = 1 To CriteriaCounter
        For j = 1 To RangeCounter
            If Cells(i + 6, 13).Value = Cells(3, j + 1).Value Then
                RunningTotal = RunningTotal + Cells(2, j + 1).Value
                RunningCount = RunningCount + 1
            End If
        Next j
    Next i
    
Range("N7").Value = RunningTotal / RunningCount


End Sub

(3) If you are trying to achieve something else entirely I suggest you be extremely descriptive.

 

[rahmaddanis]

Hello,
I decide to go for option 1, just trying to achieve VBA free workbook
Thank you very much for your assistance.