[Averageifs] Alternative with multiple criterias in multiple columns

[Lolo1313]

Hi everyone

I'm trying to make use of Averageifs with multiple criterias in multiple columns. But from what I have read so far in the forum, it's not the right function to get where I want to

I've found the following post: AVERAGEIFS with multiple criteria on one range In which T. Valko proposes =IFERROR(AVERAGE(IF(ISNUMBER(MATCH(Data!O2:O2000,S2:T2,0)),Data!Q2:Q2000)),0) for a search in one column with different criterias:.

I sadly cannot make it work for my sheet/problem and would appreciate any help
Let me explain what I'm trying to do

My main sheet looks as follows:

I want to get the average number out of all the data found in Column L , for all the "Material" in column A that matches the data from another sheet (see below) and a specific arguement from Column E (eg. " L-PHOT1").

My second sheet:

The Average number ought to be shown on a 3rd sheet that I use as a dashboard.

I hope that it is understandable... Let me know if more information is needed
Thank you for your help!

 

[Lolo1313]

Hi Fluff

Yes I think this is exactly what I want. I changed the ranges to match with my data but keep getting "0" back.


Where 'Import-Zeiten'!A2:A27961 is the range where the first criteria 'PF-Artikelgruppen'!T1:T28 needs to be met and 'Import-Zeiten'!E2:E27961 the range where the second criteria Prozessgruppen!A54 needs to be met. I tried limiting the amount of cells to 5000 and 500. Same result.
Have I overseen anything?

 

Cell Formulas
Range	Formula
D6	D6	=IFERROR(AVERAGE(IF((ISNUMBER(MATCH('Import-Zeiten'!A2:A27961,'PF-Artikelgruppen'!T1:T28,0)))*('Import-Zeiten'!E2:E27961=Prozessgruppen!A54),'Import-Zeiten'!L2:L27961)),0)

 

[Fluff]

Forgot to mention that it will need to be confirmed with Ctrl Shift Enter, rather than just Enter.

 

[Lolo1313]

That worked!
Thank you very much Fluff and all the others for helping me out!

Have a great sunday

 

[kvsrinivasamurthy]

O2=D-GRP.A

In P2 and copied down.
This will give unique list of all "Materials" (Column A). If you want other list paste those values in P column.
=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A$2:$A$20)/(ISERROR(MATCH($A$2:$A$20,$P$1:$P1,0))*($A$2:$A$20<>"")),1)),"")

In Q2 copied down
=IFERROR(AVERAGEIFS($L$2:$L$20,$A$2:$A$20,$P2,$E$2:$E$20,$O$2),"")

 

[Fluff]

Glad to help & thanks for the feedback.

 

[Lolo1313]

Hey Fluff

I was wondering if I could use another argument in the "if" function by using "ifs".
Do you think that is possible?

Greetings

 

[Fluff]

In what way?

 

[Lolo1313]

=IFERROR(AVERAGE(IF((ISNUMBER(MATCH('Import-Zeiten'!A2:A27961,'PF-Artikelgruppen'!T1:T28,0)))*('Import-Zeiten'!E2:E27961=Prozessgruppen!A54),'Import-Zeiten'!L2:L27961)),0)

The "if" function compares the value of 'Import-Zeiten'!E2:E27961 to Prozessgruppen!A54. (if I understood it correctly)
What if there was another value, e.g. Prozessgruppen!A53 that I would want to compare 'Import-Zeiten'!E2:E27961 with as well and make the average out of both

 

[Fluff]

How about

=IFERROR(AVERAGE(IF((ISNUMBER(MATCH('Import-Zeiten'!A2:A27961,'PF-Artikelgruppen'!T1:T28,0)))*(('Import-Zeiten'!E2:E27961=Prozessgruppen!A54)+('Import-Zeiten'!E2:E27961=Prozessgruppen!A53)),'Import-Zeiten'!L2:L27961)),0)

 

[Lolo1313]

Fluff, you're a blessing
Thanks alot... It works perfectly!

Have a good evening