Calculate Averages

Hi,

I need to create a formula to use each month to calculate the averages base on date ranges. I was able to create a formula to sum the data, base on the date range. However, that's just half of the equation. I need help with the countif formula, to add to my formula. Please see attached spreadsheet.

I want to be able to paste new data each month, in the grey cells, and have the averages calculate in the yellow cells.

Please help,
Dee

In your example.

B77: =SUM(IF(TEXT($A$4:$A$70,"mm-yyyy")=TEXT($B$73,"mm-yyyy"),$B$4:$B$70))/COUNT(IF(TEXT($A$4:$A$70,"mm-yyyy")=TEXT($B$73,"mm-yyyy"),1))

B78: =SUM(IF(TEXT($A$4:$A$70,"mm-yyyy")=TEXT($B$74,"mm-yyyy"),$B$4:$B$70))/COUNT(IF(TEXT($A$4:$A$70,"mm-yyyy")=TEXT($B$74,"mm-yyyy"),1))

... confirm both formulas with Ctrl+Shift+Enter

EDIT:

or

B77: =SUMPRODUCT((TEXT($A$4:$A$70,"mm-yyyy")=TEXT($B$73,"mm-yyyy"))*$B$4:$B$70)/SUMPRODUCT((TEXT($A$4:$A$70,"mm-yyyy")=TEXT($B$73,"mm-yyyy"))*1)

B78: =SUMPRODUCT((TEXT($A$4:$A$70,"mm-yyyy")=TEXT($B$74,"mm-yyyy"))*$B$4:$B$70)/SUMPRODUCT((TEXT($A$4:$A$70,"mm-yyyy")=TEXT($B$74,"mm-yyyy"))*1)

I am assuming you are calculating the average over the number of days in the month.

If you are averaging on the values in column B then:

B77: =SUM(IF(TEXT($A$4:$A$70,"mm-yyyy")=TEXT($B$73,"mm-yyyy"),$B$4:$B$70))/COUNT(IF((TEXT($A$4:$A$70,"mm-yyyy")=TEXT($B$73,"mm-yyyy"))*($B$4:$B$70<>""),1))

B78: =SUM(IF(TEXT($A$4:$A$70,"mm-yyyy")=TEXT($B$74,"mm-yyyy"),$B$4:$B$70))/COUNT(IF((TEXT($A$4:$A$70,"mm-yyyy")=TEXT($B$74,"mm-yyyy"))*($B$4:$B$70<>""),1))

... confirm both formulas with Ctrl+Shift+Enter

The Sumproduct is a clean way to get the Sumif result, without using an array formula
=SUMPRODUCT((MONTH($A$4:$A$70)=MONTH(B73))*($B$4:$B$70))

I am still working on the Countif part

It should still be able to be done without Ctrl &Shift & Enter

Do you mean something like:

B77: =SUMPRODUCT((TEXT($A$4:$A$70,"mm-yyyy")=TEXT($B$73,"mm-yyyy"))*$B$4:$B$70)/SUMPRODUCT((TEXT($A$4:$A$70,"mm-yyyy")=TEXT($B$73,"mm-yyyy"))*($B$4:$B$70<>""))

... if averaging on the values in column B and not on the number of days in the month.

Yes! Here's mine using months instead of Text

Please Login or Register  to view this content.

Did you get
10,500.19

for May?

I did indeed and using MONTH is neater.

EDIT:

... as previously posted (but using MONTH instead):

Just in case averaging over total days in the month:

B77: =SUMPRODUCT((MONTH($A$4:$A$70)=MONTH(B73))*($B$4:$B$70))/SUMPRODUCT((MONTH($A$4:$A$70)=MONTH(B73))*1)