I am looking for a complex mean formula with multiple criteria in a time period

Hi friends, I have a question.
I have a table like the one attached. For example, the date is in column C, the unit in column D and the time in column E. Now I have a field in which I enter a date. And I now need a formula which shows me the mean value of the units up to this month. The problem is: I need the mean value of the months added up.
I have in column J the units. On the basis of the field J5 and the date on H4 I would like as result: The average value (sum month 1 in the year 2022+sum month 2 in the year 2022....to the 31.05.2022 as it stands in the field H4).
There will be more years in the list later. The average should always refer from the beginning of the year to the selected date in the total.
Is something like this possible? I have now tried it with everything, but my Excel knowledge does not allow me to come up with the right formula. I would be really grateful for any suggestions for solutions!

Attachment 800436

Administrative Note:

You may NOT offer payment here on the free forum! The Commercial Services section is available if you wish to purchase points and pay for a solution.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

Your new title is not what we need.

Please use a sentence that explains what you are trying to do - NOT a collection of function names. When you have done this, please attach a sample workbook.

That's better - thank you.

There are instructions at the top of the page telling you how to attach your workbook.

By workbook, I mean workbook, not a screenshot. Waiting ...

You have not given any worked examples, but see if this works for you:

=AVERAGE(FILTER(E5:E32,(C5:C32<=H4)*(ISNUMBER(MATCH(RIGHT(D5:D32,6),UNIQUE(RIGHT(J5:J9,6)),0)))))

Auf Deutsch:

=MITTELWERT(FILTER(E5:E32;(C5:C32<=H4)*(ISTZAHL(VERGLEICH(RECHTS(D5:D32;6);EINDEUTIG(RECHTS(J5:J9;6));0)))))

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

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) 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 all those who offered help.

I am not 'picking on you' - I don't know what you mean, sorry.

No, you can't open a new thread as you are not allowed duplicate threads here - see the forum rules.

If the formula I suggested doesn't work, then please provide a version of your workbook with the results you are expecting mocked up, and someone will take another look.

Maybe this is what you want:

=AVERAGE(FILTER($E$5:$E$32,($C$5:$C$32<=$H$4)*(RIGHT($D$5:$D$32,6)=RIGHT(J5,6))))

Or this, perhaps:

=SUM(FILTER($E$5:$E$32,($C$5:$C$32<=$H$4)*(RIGHT($D$5:$D$32,6)=RIGHT(J5,6))))

What is supposed to be in the cells showing a #REF! error?

Try this:

=LET(i,INDEX(SUMIFS($E$4:$E$32,$D$4:$D$32,RIGHT($J5,6),$C$4:$C$32,"<="&$H$4,$C$4:$C$32,UNIQUE(FILTER($C$5:$C$32,$C$5:$C$32<=$H$4))),),AVERAGE(i))

and this:

=LET(i,INDEX(SUMIFS($E$4:$E$32,$D$4:$D$32,RIGHT($J11,6),$C$4:$C$32,"<="&$H$10,$C$4:$C$32,UNIQUE(FILTER($C$5:$C$32,$C$5:$C$32<=$H$10))),),AVERAGE(i))

It looks like you have seen this and looked at the file I attached.

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

Glad to have helped.