ISSUES with SUMIFS

to sum as an and it will exclude if either condition is not met
a7=5
and the last 4 values are 2021 so it performs correctly

=SUM($C$3:$C$58)-SUMIFS($C$3:$C$58, $A$3:$A$58, $G$4, $B$3:$B$58, $G$5)

gives the answer you require

Hi
thank you for that

can you please advise how to calculate average for the same range

I got official excel table which changes each week which means next week I will have values with CW6 etc ... and I need to exclude from calculation all values that are before CW5 in year 2021.
what I would like to get as average is average of values from CW up to CW5 (so after two week I would need to exclude CW6 and CW7 as well)

as the example: how can I calculate average up to CW39 and make sure that excel will only calculate average up to CW39 and will not include into calculation CW40, 41, etc?

hope that make sense ...

thanks in advance for solution

Hi
sorry for confusing ...

basically my file is automatically updating and each week I got another line added to the file and what I want to is to exclude from average calculation all values that are meeting criteria
for instance if criteria are:
CW: less than CW 49
Year: 2020
I need to calculate all values in the rage till CW49 year 2020 and exclude all what is after CW49 year 2020

however in case my criteria are:
CW less than 3
Year 2021
I need to calculate average up to CW 3 Year 2021 but also include all what is in the past meaning in year 2020

Basically I need to take into account only these values that are up to certain CW (defined as criteria) and exclude everything what is after that CW

so if my criteria is CW 4 I want to calculate average up to CW 4 (all above) but exclude all what is after CW 4.
problem is that file has CW 4 twice - in 2020 and 2021

hope that make sense

thanks

You are leaving too many gaps Is the range always continuous in your sheet? If so the below should work and be ok if you dont to have too many other calculations

It find the first value(beginning year and week) and the row it appears on, and does likewise for the second value (end year and week)

It uses this to create a range between them, then works out the average of the range

Hi
thanks