View original (French)

AVERAGEIFS with date range criteria

clman -  
Vaucluse Posted messages 27336 Registration date   Status Contributeur Last intervention   -
Hello,

I am trying to create averages based on two criteria:
- the first being a text (=AVERAGEIFS(A1:A10,B1:B10,"Object") which I am able to do
- the second being a date range criterion. I tried to add (....,"Object",C1:C10,">=01/01/2018<=31/01/2018") which gives me an average result identical to the first formula as if it does not take my date criterion into account

I hope I have been clear enough,
Thank you in advance

Clément

Configuration: Windows / Firefox 59.0

1 réponse

Réponse 1 / 1
Vaucluse Posted messages 27336 Registration date   Status Contributeur Last intervention   6 453
 
Hello

=AVERAGEIFS(A1:A10,B1:B10,"object",C1:C10,">=1/1/2018",C1:C10,"<=31/1/18")

You can also use cell references for the date limits
for example start in Z1 and end in Z2:

=AVERAGEIFS(A1:A10,B1:B10,"object",C1:C10,">="&$Z$1,C1:C10,"<="&$Z$2)

or to get the average for a month if you only have one year:

=SUMPRODUCT((B1/B10="object")*(MONTH(C1:C10)=1)*(A1:A10))/SUMPRODUCT((B1:B10="object")*(MONTH(C1:C10)=1))

of course 1 for January etc...

best regards

--
The quality of the answer mainly depends on the clarity of the question, thank you!
1